About dated if function

Closed
taghreed almuhajiri Posts 13 Registration date Saturday March 8, 2014 Status Member Last seen March 19, 2014 - Mar 17, 2014 at 04:22 AM
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 - Mar 19, 2014 at 10:26 AM
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

7 replies

Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 17, 2014 at 08:53 AM
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.
0
taghreed almuhajiri Posts 13 Registration date Saturday March 8, 2014 Status Member Last seen March 19, 2014
Mar 17, 2014 at 09:54 AM
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
0
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 17, 2014 at 12:06 PM
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.
0
taghreed almuhajiri Posts 13 Registration date Saturday March 8, 2014 Status Member Last seen March 19, 2014
Mar 18, 2014 at 03:02 AM
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" !!!!
0

Didn't find the answer you are looking for?

Ask a question
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 18, 2014 at 10:24 AM
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
0
taghreed almuhajiri Posts 13 Registration date Saturday March 8, 2014 Status Member Last seen March 19, 2014
Mar 19, 2014 at 01:58 AM
God, this program is so complicated

anyway it worked, thanks again
appreciated
0
Mazzaropi Posts 1980 Registration date Monday August 16, 2010 Status Contributor Last seen December 22, 2022 146
Mar 19, 2014 at 10:26 AM
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
0