Years Months Days 1

[mrkuosea]

How can I change a number like 3.80 into 3 Years 10 Months ?? Days?

Thank you all.

 

[mintjulep]

Using your mouse or arrow keys, position your cursor immediately to the right of the text "3.80".

Press the backspace key 4 times.

Type "3 Years 10 Months ?? Days?" using the relevant keys on the keyboard.

 

[mrkuosea]

Very funny MintJulep!

You know I meant 'convert'!

 

[SkipVought]

hi,

I like mint's suggestion. But I think that you wanted a little more. Left me suggest a TIP.

Assume that 3.8 is in A1.

In A2 this formula for years and months. Use a similar approch for the days
[tt]
A2: =INT(A1)&" days "&INT((A1-INT(A1))*12)+1&" months"
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, what do you think the number of days should be?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mrkuosea]

Here's the math

1 ÷ 12 = 0.08
2 ÷ 12 = 0.17
3 ÷ 12 = 0.25
4 ÷ 12 = 0.33
5 ÷ 12 = 0.42
6 ÷ 12 = 0.50
7 ÷ 12 = 0.58
8 ÷ 12 = 0.67
9 ÷ 12 = 0.75
10 ÷ 12 = 0.83
11 ÷ 12 = 0.92
12 ÷ 12 = 1.00

So for 0.80, it should be 9 months and 18 to 24 days?

 

[SkipVought]

I beg to differ.
[tt]
1 ÷ 12 =0.00 - 0.08
2 ÷ 12 =0.09 - 0.17
3 ÷ 12 =0.18 - 0.25
4 ÷ 12 =0.26 - 0.33
5 ÷ 12 =0.34 - 0.42
6 ÷ 12 =0.43 - 0.50
7 ÷ 12 =0.51 - 0.58
8 ÷ 12 =0.59 - 0.67
9 ÷ 12 =0.68 - 0.75
10 ÷ 12 =0.76 - 0.83
11 ÷ 12 =0.84 - 0.92
12 ÷ 12 =0.92 - 1.00
[/tt]
So .8 would be 10 months

But, again, HOW MANY DAYS??? I know!



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Actually a much simpler way would be to convert years to days, and then use date functions to extract the YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.

3.8 years is 1387.95 days.

years is YEAR(1387.95)-1899
months is MONTH(1387.95)
days is DAY(1387.95)
hours is HOUR(1387.95)
minutes is MINUTE(1387.95)
seconds is SECOND(1387.95)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Actually, given that the average Gregorian calendar-year is 365.2425 days, 3.8 years = 1387.9215 days.

Regardless, we already know we've got 3 whole years, so all that's left to resolve is the 0.8 year (nominally 292.194 days). If we divide that by the average month length (30.436875 days), we end up with 9.6 'average' months, or 9 months and 18.262125 days.

All of which is pretty academic if you're wanting to relate anything of th calculation to a calendar, in which case you need to deal with actual month lengths.

Cheers
Paul Edstein
[MS MVP - Word]

 

[mrkuosea]

Looks like it is finally figured out

Cell A1: 6.196

Enforce 3 decimal places on cell A1 by using =FIXED(A1,3)[optional step]

=INT(A1)&" Years " & INT(((ROUND(((A1-INT(A1))*360),0))/360)*12)& " Months " & ROUND(MOD((ROUND(((A1-INT(A1))*360),0)),30),0) & " Days "

Result: 6 Years 2 Months 11 Days

It may not be perfect but it works!

Thanks guys!

 

[macropod]

Hi mrkuosea,

A simpler formula for you to try:
=INT(A1)&" Years "&INT(MOD(A1,1)*12)&" Months "&ROUND(MOD(A1*12,1)*30.436875,0)&" Days"

Cheers
Paul Edstein
[MS MVP - Word]

 

[lionelhill]

for a very simple formula:

= A1 * 365.2425

Then format the cell with a custom format
yy " years " m " months"

Add days etc. to taste. Sadly this will give a leading zero to the years (for some reason "y" changes to "yy" and displays 03).
In deference to Skip, I should point out this merely displays the appropriate wordage but doesn't change the underlying value, which is a number of days, which I'm abusing by treating as a date).

 

[mrkuosea]

Macropod,

Your formula is good and simple but it has a small problem.

When you try to use it for a number like 4.083 it results in 4 years 0 Months and 30 Days instead of 4 Years 1 Months 0 days.

Thanks for the suggestion anyway.

 

[SkipVought]

No, actually it IS 0 months 30 days.

I have been thinking about this.

If you use the DATE mothod, you get 1/30/1904. Well, think about it. If the day is within the first month of days, then one month has not transpired. unlike DATE counting, which starts with Month 1, DURATION counting starts with ZERO. If the days durations is less than one month, then it is zero months.

So, using the DATE method you must subtract ONE from the month.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi mrkuosea,

Given that an 'average' month is 30.436875 days, any value that rounds to 30 days is less than a month. Thus, for an input of 4.083 years, 4 years 0 Months and 30 Days is correct, whereas 4 Years 1 Months 0 days is not.

Cheers
Paul Edstein
[MS MVP - Word]

 

[lionelhill]

... so the real problem is that a month isn't a defined length of time (because it could be 28, 29, 30 or 31 days, depending on which month). It would make more sense to say "3 years and 10 Septembers". This means the original question, sadly, is ambiguous, and cannot be answered as written.

 

[macropod]

Minor formula enhancement:
=INT(A1)&" Years "&MOD(INT(A1*12),12)&" Months "&INT(MOD(A1*12,1)*30.436875)&" Days"

Note: This still renders 4.083 years as 4 years 0 Months and 30 Days, but now (correctly) renders 4+1/12 (4.08333˙) years as 4 Years 1 Months 0 Days.

Cheers
Paul Edstein
[MS MVP - Word]