Days, Moths and Years in Excel

[Livia8]

Hi tehre,

I'm trying to find a function that would turn a figure (let's say 33.562) into 33 Years, nn Months and nn days.

I'm sure it's probably something straight forward, but I can't see it.

Thanks.

Livia

 

[mintjulep]

There is no native function for such a thing.

A formula might work. What is the conversion from .562 to months and days?

 

[SkipVought]

Hi,

Trouble is, Year and Month are imprecise spans.

You could get an OK approximation by converting years to days using 365.25 days per year and then adding that value to some date, like today, and finally calculating the number of years, months and days.

Reuslts:
[tt]
Years Days Dates Year Month Day
33.562 12258.5 11/14/2008 2008 11 14
06/07/2042 2042 6 7

34 -5 -7
-1 -1
33 6 23
CheckDays
12258.9 12053.3 182.6 23
[/tt]
formulas:
[tt]
Years Days Dates Year Month Day
33.562 =A2*DaysPerYr =TODAY() =YEAR(C2) =MONTH(C2) =DAY(C2)
=C2+B2 =YEAR(C3) =MONTH(C3) =DAY(C3)

=D3-D2 =E3-E2 =F3-F2
=IF(E5<0,-1,0) =IF(F5<0,-1,0)
=SUM(D56)
=IF(SUM(E5:E6)<0,12+SUM(E5:E6),SUM(E5:E6))
=IF(SUM(F5:F6)<0,30+SUM(F5:F6),SUM(F5:F6))
CheckDays
=SUM(D9:F9) =D7*DaysPerYr =E7*DaysPerYr/12 =F7
[/tt]
all on row 7

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}