From number of days to Years, months and days DATEDIFF madness!!

[zozew]

Hi Everybody,

I have this great little sheet doing some calculations with VBA and excel formulas. All is well until I decided to convert the resulting days into yers months and days..

=DATEDIF(0,3675,"y") & " years, " & DATEDIF(0,3675,"ym")&" months, " & DATEDIF(0,3675,"md")&" days"

And the result is below...all look great right....NO!!!! its supposed to be 25 days not 22!!!

10 years, 0 months, 22 days

(the 3675 above comes from a cell D35)

I've checked with various calculators on google and the resulting days should be 25

Can anyone please help me keep some of my hair....

Much appreciated and

 

[mrshl9898]

You sure about that?

8 years has to be 2922 days (365x8 + 2), 2 will be either 730 (365x2) or 731 (365x2 + 1), leaving 23 or 22 days as the balance

 

[zozew]

well.....

3675 days to years - Unit Converter

unitconverter.io

 

[zozew]

Crap!!...you are right why is it like this.. this one gives me 23 days... but I have a colleague that has calculated it to 25 as well...so I'm so confused now

Online calculator: Convert days to years, months, and days

This calculator converts a number of days to a number of years, months, and days in the month.

planetcalc.com

 

[eduzs]

Try: (I imagined it now, not very tested, may not work as expected or in all situations):
A1 = Date 1
B1 = Date 2
A2 formula (years) =DATEDIF(A1,B1,"y")
B2 formula (months) =DATEDIF(EDATE(A1,A2*12),B1,"m")
C2 formula (days) =DATEDIF(EDATE(EDATE(A1,A2*12),B2),B1,"d")

 

[zozew]

Can you decipher the reason why this is....some kind of leap year thing?? or...would be nice to be able to have an argument for one or the other when I start to debate this haha

 

[zozew]

Cool! ill try this...

So if I only have one date/number basically A1 should be 0 then

Try: (not tested, may not work as intended):
A1 = Date 1
B1 = Date 2
A2 formula (years) =DATEDIF(A1,B1,"y")
B2 formula (months) =DATEDIF(EDATE(A1,A2*12),B1,"m")
C2 formula (days) =DATEDIF(EDATE(EDATE(A1,A2*12),B2),B1,"d")

 

[eduzs]

Datadif is used like =datadif(date1;date2;"y or m or a").
The diference between date 0 (1/1/1900) and date 3675 (22/1/1910) is 3675 days.
I don't know the technical details of this function or the date count for this period. Sorry.
Maybe someone else can help.

 

[zozew]

actually there are 3 more functions MD YM YD

 

[zozew]

interesting your code gives the same result as mine...22days