| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 minute
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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 mi
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! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 minute
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) -- Daryl S "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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 mi
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" wrote: 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! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 mi
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" -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: 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" wrote: 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! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 mi
Thank you very much, Luke!
"Luke M" wrote: 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" wrote: 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! "Luke M" wrote: 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 -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Format 8.3568 into: 8 years, 4 months, 8 days, 10 hours, 45 mi
Thanks for the comment, Daryl!
"Daryl S" wrote: 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) -- Daryl S "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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| years-months-days-hours | Excel Discussion (Misc queries) | |||
| converting days to years & months & days | Excel Discussion (Misc queries) | |||
| how do i convert a number of days to years, months & days? | Excel Discussion (Misc queries) | |||
| How do I convert a number into Years, Months, Days format? | Excel Worksheet Functions | |||
| convert Days to Years, Months, Days | Excel Discussion (Misc queries) | |||
Linear Mode
