Datedif formula not accurate

[gino59]

Hi all...

Got some great help earlier on the DATEDIF formula, but it doesn't appear to be very accurate.

In cell I12 - (formatted as a date) 5/27/1929
In cell I13 - (formatted as a date) 1/3/2004

Cell I14 Result is 74 years, 7 months, 120 days. ????

Result should be 74 years, 7 months, 7 days.

If I change I13 to a date like 1/31/2004 - I get 74 years, 8 months, 4 days which is accurate. Not sure why the day count comes up as 120 days in the first example.

The formula is:

=DATEDIF(I12,I13,"y")&" years, "&DATEDIF(I12,I13,"ym")&" months, "&DATEDIF(I12,I13,"md")&" days"

Thank you!!

Gino

 

[VoG]

Perhaps http://www.mrexcel.com/forum/showthread.php?t=302028

 

[gino59]

Wow, Peter - looks like you've been down this road before!!

So, I'm using the one I saw you posted -

=YEAR(D7)-YEAR(C7)-(TEXT(D7,"mmdd")< TEXT(C7,"mmdd"))&" years "&MOD(MONTH(D7)-MONTH(C7)-(DAY(D7)< DAY(C7)),12)&" months "&D7-DATE(YEAR(D7),MONTH(D7)-(DAY(D7)< DAY(C7)),DAY(C7))&" days"

Seems to return the correct value in this particular instance. I'm thinking it might be better to replace my DATEDIF formulas entirely with this (right now, I'm using the formula for about 2000 rows!). That many rows and my eyes are glazing over trying to "validate" the results!

Many thanks as always for your generous help!

Cheers!
Gino

 

[VoG]

The person to thank really is barry houdini - an absolute genius at date/time issues.

 

[gino59]

Yeah, Barry's solutions have bailed my butt out many times already! Maybe now we know why DATEDIF isn't an "official" formula in Excel, eh?

I think I've got a bit more faith in Barry's/your formula solution. Might be longer but it appears to break every component of the date out one by one and do the simple math on them.

Again, thanks and best wishes!

Gino