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!

 

[Luke M]

It would help if we knew what your logic was. Why does 8.3568 equal that
specific time frame?

Normally, that time frame, which could be written as:
4/8/1908 10:45:07 AM

Would have a numerical value in XL of 3021.447998

 

[igorin]

Hello, Luke,

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!

 

[Daryl S]

Igorin -

If you use a 360-day year, then the decimal year is converted as follows:

Here are the assumptions:
Months in a Year 12
Days in a year 360
Hours in a day 24
Minutes in an hour 60
Seconds in a Minute 60

Decimal Years 8.3568
Years 8.00 (integer portion of line above)
Remaining Years 0.3568 (difference of two lines above)
Months 4.00 (integer portion of line above divided by 12)
Remaining Years 0.0234667 (difference between .3568 and 4/12)
Days 8.00 (integer portion of line above * 360)
Remaining Years 0.001244 (difference between .0234667 and 8/360)
Remaining Minutes 645.120000 (line above * 360*24*60)
Hours 10.00 (integer portion of line above / 60)
Remaining Minutes 45.1200 (difference of two lines above)
Minutes 45.00 (integer portion of line above)
Remaining Minutes 0.12 (difference of two lines above)
Seconds 7.00 (integer portion of line above * 60)

 

[Luke M]

Thanks for clearing that up. It might be easier to split this into multiple
cells for clarity, but for the one-cell formula would be:

=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
"&INT(MOD(MOD(MOD(MOD(MOD(A1,1)*12,1)*30,1)*24,1)*60,1)*60)&" seconds"

 

[Luke M]

Copied from Glenn's post on your other thread:

=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 "&
INT(MOD(A1*12*30*24*60,1)*60)&" Seconds"

 

[igorin]

Thank you very much, Luke!

Thanks for clearing that up. It might be easier to split this into multiple
cells for clarity, but for the one-cell formula would be:

=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
"&INT(MOD(MOD(MOD(MOD(MOD(A1,1)*12,1)*30,1)*24,1)*60,1)*60)&" seconds"

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

 

[igorin]

Thanks for the comment, Daryl!

Igorin -

If you use a 360-day year, then the decimal year is converted as follows:

Here are the assumptions:
Months in a Year 12
Days in a year 360
Hours in a day 24
Minutes in an hour 60
Seconds in a Minute 60

Decimal Years 8.3568
Years 8.00 (integer portion of line above)
Remaining Years 0.3568 (difference of two lines above)
Months 4.00 (integer portion of line above divided by 12)
Remaining Years 0.0234667 (difference between .3568 and 4/12)
Days 8.00 (integer portion of line above * 360)
Remaining Years 0.001244 (difference between .0234667 and 8/360)
Remaining Minutes 645.120000 (line above * 360*24*60)
Hours 10.00 (integer portion of line above / 60)
Remaining Minutes 45.1200 (difference of two lines above)
Minutes 45.00 (integer portion of line above)
Remaining Minutes 0.12 (difference of two lines above)
Seconds 7.00 (integer portion of line above * 60)