Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
I'm using this function to calculate date difference, but just stumbled upon
something that's making me think it's wrong. Here it is: =DATEDIF(B3,C3,"y") & " years, " & DATEDIF(B3,C3,"ym") & " months, " & DATEDIF(B3,C3,"md") & " days" It seemed to work fine until I entered the same date into the two cells, and what returned was: 0 years, 0 months, 0 days. This seems odd to me. Shouldn't that be 0 years, 0 months, 1 days. Why doesn't the function count the "zero date" as one? How should I modify it so I get an accurate result? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
Surely the date difference between two dates which are the same IS zero.
There are no dates different. What would two dates 1 day apart show? 2? Not sure I follow you -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS www.nickhodge.co.uk "jmj713" wrote in message ... I'm using this function to calculate date difference, but just stumbled upon something that's making me think it's wrong. Here it is: =DATEDIF(B3,C3,"y") & " years, " & DATEDIF(B3,C3,"ym") & " months, " & DATEDIF(B3,C3,"md") & " days" It seemed to work fine until I entered the same date into the two cells, and what returned was: 0 years, 0 months, 0 days. This seems odd to me. Shouldn't that be 0 years, 0 months, 1 days. Why doesn't the function count the "zero date" as one? How should I modify it so I get an accurate result? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
Surely the date difference between two dates which are the same IS zero.
There are no dates different. What would two dates 1 day apart show? 2? Not sure I follow you Logically, yes. But not in my case, because I need this formula to deternime how long a person has been a client. If they came in only once, and never again, they were our client for one day. Not zero days. You see? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
Maybe you could just add 1 to each C3:
=DATEDIF(B3,C3+1,"y") & " years, " & DATEDIF(B3,C3+1,"ym") & "months, " & DATEDIF(B3,C3+1,"md") & " days" jmj713 wrote: I'm using this function to calculate date difference, but just stumbled upon something that's making me think it's wrong. Here it is: =DATEDIF(B3,C3,"y") & " years, " & DATEDIF(B3,C3,"ym") & " months, " & DATEDIF(B3,C3,"md") & " days" It seemed to work fine until I entered the same date into the two cells, and what returned was: 0 years, 0 months, 0 days. This seems odd to me. Shouldn't that be 0 years, 0 months, 1 days. Why doesn't the function count the "zero date" as one? How should I modify it so I get an accurate result? Thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
Then just add one to your formula.
-- Regards, Fred "jmj713" wrote in message ... Surely the date difference between two dates which are the same IS zero. There are no dates different. What would two dates 1 day apart show? 2? Not sure I follow you Logically, yes. But not in my case, because I need this formula to deternime how long a person has been a client. If they came in only once, and never again, they were our client for one day. Not zero days. You see? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
Or Dave...?
IF(B3=C3,1,DATEDIF(OrigFormulahere)) If a time is involved you could use DATEVALUE around each B3,C3. Obviously the 1 could be expanded to a string to match 'look' of DATEDIFs return 'x years x months x years' -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS www.nickhodge.co.uk "Dave Peterson" wrote in message ... Maybe you could just add 1 to each C3: =DATEDIF(B3,C3+1,"y") & " years, " & DATEDIF(B3,C3+1,"ym") & "months, " & DATEDIF(B3,C3+1,"md") & " days" jmj713 wrote: I'm using this function to calculate date difference, but just stumbled upon something that's making me think it's wrong. Here it is: =DATEDIF(B3,C3,"y") & " years, " & DATEDIF(B3,C3,"ym") & " months, " & DATEDIF(B3,C3,"md") & " days" It seemed to work fine until I entered the same date into the two cells, and what returned was: 0 years, 0 months, 0 days. This seems odd to me. Shouldn't that be 0 years, 0 months, 1 days. Why doesn't the function count the "zero date" as one? How should I modify it so I get an accurate result? Thanks -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date difference = zero?
If starting on Oct 19, 2006 and finishing Oct 19, 2006 meant that it should
count as 1 day, then I figured starting on Oct 19, 2006 and finishing on Oct 20, 2006 should count as 2 days. But who knows if that matches the OP's requirements?? Nick Hodge wrote: Or Dave...? IF(B3=C3,1,DATEDIF(OrigFormulahere)) If a time is involved you could use DATEVALUE around each B3,C3. Obviously the 1 could be expanded to a string to match 'look' of DATEDIFs return 'x years x months x years' -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England HIS www.nickhodge.co.uk "Dave Peterson" wrote in message ... Maybe you could just add 1 to each C3: =DATEDIF(B3,C3+1,"y") & " years, " & DATEDIF(B3,C3+1,"ym") & "months, " & DATEDIF(B3,C3+1,"md") & " days" jmj713 wrote: I'm using this function to calculate date difference, but just stumbled upon something that's making me think it's wrong. Here it is: =DATEDIF(B3,C3,"y") & " years, " & DATEDIF(B3,C3,"ym") & " months, " & DATEDIF(B3,C3,"md") & " days" It seemed to work fine until I entered the same date into the two cells, and what returned was: 0 years, 0 months, 0 days. This seems odd to me. Shouldn't that be 0 years, 0 months, 1 days. Why doesn't the function count the "zero date" as one? How should I modify it so I get an accurate result? Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difference between date and times | Excel Discussion (Misc queries) | |||
Mileage Claim Formula | New Users to Excel | |||
IF and Date Functions | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Difference of date | New Users to Excel |