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.

--
Rick (MVP - Excel)


"igorin" wrote in message
...
Thank you very much for the help. Glenn!!!

"Glenn" wrote:

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"


Glenn wrote:
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"

igorin wrote:
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!



"igorin" wrote:

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!
.