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

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!

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

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!

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

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

On Feb 11, 4:33*pm, 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!- Hide quoted text -

- Show quoted text -

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

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!

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

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!

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

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" wrote in message
...
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!

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

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

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

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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!


.

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

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