Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 minute

[igorin]

Hello,

How can I format the number 8.3568 into:

8 years, 4 months, 8 days, 10 hours, 45 minutes and 7 seconds?

Thank you!

 

[Matt Richardson]

Hello,

How can I format the number 8.3568 into:

8 years, 4 months, 8 days, 10 hours, 45 minutes and 7 seconds?

Thank you!

How does that number relate to the details you've specified?

BW
Matt
http://teachr.blogspot.com

 

[igorin]

To further explain, what follows is the logic behind it:

original number: 3,050.232
which equals to:
years: 3,050.232 / 365 = 8.3568
months: 8.3568 - 8(whole years) = 0.3568 -> 0.3568 * 12 = 4.2816
days: 4.2816 - 4(whole months) = 0.2816 -> 0.2816 * 30 = 8.448
hours: 8.448 - 8(whole days) = 0.448 -> 0.448 * 24 = 10.752
minutes: 10.752 - 10(whole hours) = 0.752 -> 0.752 * 60 = 45.12
seconds: 45.12 - 45(whole minutes) = 0.12 -> 0.12 * 60 = 7.2

Thanks for the help!

 

[Pete_UK]

Well, you have the calculations there. You just need to know that the
INT function will give you the integer value of a number, and MOD will
give you the remainder after division, and then you will be able to
construct your formula.

Hope this helps.

Pete

 

[Glenn]

One way:

=INT(A1)&" Years, "&INT(MOD(A1,1)*12)&" Months, "&
INT(MOD(MOD(A1,1)*12,1)*30)&" Days, "&
INT(MOD(MOD(MOD(A1,1)*12,1)*30,1)*24)&" Hours, "&
INT(MOD(MOD(MOD(MOD(A1,1)*12,1)*30,1)*24,1)*60)&" Minutes and "&
ROUND(MOD(MOD(MOD(MOD(MOD(A1,1)*12,1)*30,1)*24,1)*60,1)*60,1)&" Seconds"

 

[Glenn]

I think my "MOD" key was stuck...

=INT(A1)&" Years, "&INT(MOD(A1,1)*12)&" Months, "&
INT(MOD(A1*12,1)*30)&" Days, "&
INT(MOD(A1*12*30,1)*24)&" Hours, "&
INT(MOD(A1*12*30*24,1)*60)&" Minutes and "&
ROUND(MOD(A1*12*30*24*60,1)*60,1)&" Seconds"

 

[Bernie Deitrick]

igorin,

You cannot just format the cell to get that. With your value in cell A2

8.3568
Use this in A3 to get full years: =INT(A2)
Use this in A4 to get months (assume that 1 month is 1/12 of a year):
=INT((A2-A3)*12)
Use this in A5 to get days (365 days per year): =INT((A2-A3-A4/12)*365)
Use this in A6: =A2-A3-A4/12-A5/365

Format A6 for HH:MM:SS.

HTH,
Bernie
MS Excel MVP

 

[igorin]

Thank you very much for the help. Glenn!!!

I think my "MOD" key was stuck...

=INT(A1)&" Years, "&INT(MOD(A1,1)*12)&" Months, "&
INT(MOD(A1*12,1)*30)&" Days, "&
INT(MOD(A1*12*30,1)*24)&" Hours, "&
INT(MOD(A1*12*30*24,1)*60)&" Minutes and "&
ROUND(MOD(A1*12*30*24*60,1)*60,1)&" Seconds"


.

 

[igorin]

Thank you, Bernie. Glenn's answer was what I was looking for.
Thank you very much, anyway!

 

[Rick Rothstein]

Just so you are aware... the formula Glenn gave you (which is based on your
posted calculation) will only yield a meaningful result if the original
number (3,050.232 for your example) was calculated in reverse the same way.
If, on the other hand, the original number was calculated by simply
subtracting two date values, then the odds of the formula yielding
meaningful results is slim. Why? Because you used months as one of the
parameters. If you simply subtracted two dates, then which dates matter in
the calculation because some months have 30 days, others 31 and February
either 28 or 29 depending on the year... which dates are bridged would then
affect the final calculation.