About dated if function

[Closed]
Report
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014
-
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
-
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

Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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.
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014

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
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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.
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014

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" !!!!
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014

God, this program is so complicated

anyway it worked, thanks again
appreciated
Posts
1943
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
September 22, 2021
145
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