|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 11th, 2010, 03:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
February 11th, 2010, 04:33 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#3
February 11th, 2010, 04:39 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 -
|
|
#4
February 11th, 2010, 04:41 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#5
February 11th, 2010, 04:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#6
February 11th, 2010, 04:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!
|
|
#7
February 11th, 2010, 06:13 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! .
|
|
#8
February 11th, 2010, 06:15 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! .
|
|
#9
February 11th, 2010, 06:34 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
