Prev: Can I 'embed' a formula to affect numbers as they're entered?
Next: IF THEN Statement
From: igorin on 11 Feb 2010 10:56
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!
From: igorin on 11 Feb 2010 11:33
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!
From: Pete_UK on 11 Feb 2010 11:39
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 <igo...(a)discussions.microsoft.com> 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 -

From: Glenn on 11 Feb 2010 11:41
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!
From: Glenn on 11 Feb 2010 11:46
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!
 |  Next  |  Last
Pages: 1 2
Prev: Can I 'embed' a formula to affect numbers as they're entered?
Next: IF THEN Statement