About dated if function Closed

Question

Hey again,

i had to use this formula to find the deference between two dates 

=DATEDIF(E4,F4,"d")+(1)
 
it shows me (1) in all the empty cells instead of (0) (that's ok)

but in the total section, i got this result (the number +1)

I need to know what function that counts ONLY the value in the cell not any numbers inside the formula

Or  what's the opposite of count function ??

thanks

Mazzaropi · Answer

taghreed almuhajiri, Good morning.
I believe there is nothing strange with the formula.

=DATEDIF(E4,F4,"d")....+....(1)
 
This formulas does:
1st part: Difference between two dates with answer in days
2nd part: adds 1 to the result

then:
a) E4 = 03/01/2014 and F4 = 03/17/2014
=DATEDIF(E4,F4,"d")....+....(1)
................16................+.....1
--> = 17

b) E4 = 01/01/2014  and F4  = 01/01/2014 - Both same date
=DATEDIF(E4,F4,"d")....+....(1)
................0..................+.....1
--> = 1

c) E4 = Empty  and F4  = Empty 
...excel treats this empty as 0 (Zero)
=DATEDIF(E4,F4,"d")....+....(1)
................0..................+.....1
--> = 1

Conclusion: This formula works perfectly.

BUT...You said: 
"...it shows me (1) in all the empty cells instead of (0) (that's ok)..." 
Then I can't understand your doubt.

Please, could you give us some real example for our better understanding about the problem?

I hope it helps.
Have a nice day.

taghreed almuhajiri · Answer

Good morning,

this part like i said before is ok (to show 1 in all empty cells)

the problem here with the Total part

Ex :

E4 = 1-Jan and F4 = 5-Jan
G4 =5  -------------> =DATEDIF(E4,F4,"d")+(1)



I4 = 1-Feb and J4 = 5-Feb
K4 =5 --------------> =DATEDIF(I4,J4,"d")+(1)


M4 = 1-Mar and N4 = 5-Mar
O4 =5 --------------> =DATEDIF(M4,N4,"d")+(1)


lets say i have to count the total in Q4 ( G4+K4+O4)
its saying 18
it should be only 15 as you know
it counts 5 + 5 + 5 + (1) in the formula *3

i hope you can tell me how to fix it

thank you

Mazzaropi · Answer

taghreed almuhajiri, Good morning. 

E4 = 1-Jan and F4 = 5-Jan
--> G4 =5 -------------> =DATEDIF(E4,F4,"d")+(1)
I4 = 1-Feb and J4 = 5-Feb
--> K4 =5 --------------> =DATEDIF(I4,J4,"d")+(1)
M4 = 1-Mar and N4 = 5-Mar
--> O4 =5 --------------> =DATEDIF(M4,N4,"d")+(1) 

All these formulas above are correct.

Remember you're using the (+1) part only on a DATADIF  line formula.
The results: G4=5; K4=5; O4=5 are OK.

Then if you do:  
Q4 --> =(G4 + K4 + O4) 
Q4 --> =(..5. + .5. + .5.) 
The result MUST be 15. Not 18.
Are you sure that you're using this formula above to obtain a result?
It sounds very strange.

Save your file at any free site and put a link here to download.
We need to see it to help you better.

taghreed almuhajiri · Answer

i did copy your formulas just to make sure , and i found that when the 3 parts are full it shows 15 "correct"
BUT  when i removed one of them its (11) as you see


http://www.megafileupload.com/en/file/510021/test-xlsx.html


is't normal "excel does that" !!!!

Mazzaropi · Answer

taghreed almuhajiri, Good morning.

"...is't normal "excel does that" !!!!..."
Sorry, but it's not normal.
Excel don't makes mistakes.
What happens is that our formulas, a lot of times, have a logic problem.
Then the result is not what we expect.

Now we saw your file the problem was solved.

You can control when a formula may work.
The formula was acting even with empty cells.
So always generated an error as you pointed at O4.

Try to do:
Before: G4 --> =DATEDIF(E4,F4,"d")+(1)
Now...: G4 --> =IF(AND(E4>0,F4>0),DATEDIF(E4,F4,"d")+(1),"")

Before: K4 --> =DATEDIF(I4,J4,"d")+(1)
Now...: K4 --> =IF(AND(I4>0,J4>0),DATEDIF(I4,J4,"d")+(1),"")

Before: O4 --> =DATEDIF(M4,N4,"d")+(1)
Now...: O4 --> =IF(AND(M4>0,N4>0),DATEDIF(M4,N4,"d")+(1),"")

Please, tell us if it worked for you.
I hope it helps.
 --
Belo Horizonte, Brasil.
Marcílio Lobão

taghreed almuhajiri · Answer

God, this program is so complicated

anyway it worked, thanks again
appreciated

Mazzaropi · Answer

taghreed almuhajiri, Good morning. 

taghreed almuhajiri, not be discouraged.
Excel is not complicated.
Everything is a matter of understanding how the logic of data processing works.

Perhaps you are beginning to use Excel with the need to develop a complex task.
Then it gets a little harder, learn to use the program and at the same time develop a more complex product to work.

An excellent and free method to learn a lot about Excel formulas, always use the HELP program for each function.
He is very good and has real examples of how to make it work.

If you need, there will always be this forum with several people with great willingness to help.

Have a nice day. 
 --
Belo Horizonte, Brasil.
Marcílio Lobão

About dated if function

7 responses

Similar discussions

Subscribe To Our Newsletter!