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 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 19, 2014 at 10:26 AM
Mazzaropi Posts 1985 Registration date Monday August 16, 2010 Status Contributor Last seen May 24, 2023 - Mar 19, 2014 at 10:26 AM
Related:
- About dated if function
- Agp function - Guide
- Network card function - Guide
- Find function on mac - Guide
- Function of insert key - Guide
- Accessor function c++ - Guide
7 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 17, 2014 at 08:53 AM
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.
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
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014
Mar 17, 2014 at 09:54 AM
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
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
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 17, 2014 at 12:06 PM
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.
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
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014
Mar 18, 2014 at 03:02 AM
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" !!!!
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" !!!!
Didn't find the answer you are looking for?
Ask a question
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 18, 2014 at 10:24 AM
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
"...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
Posts
13
Registration date
Saturday March 8, 2014
Status
Member
Last seen
March 19, 2014
Mar 19, 2014 at 01:58 AM
Mar 19, 2014 at 01:58 AM
God, this program is so complicated
anyway it worked, thanks again
appreciated
anyway it worked, thanks again
appreciated
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Mar 19, 2014 at 10:26 AM
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
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
