Year/Month/Date Question

[Mike]

Is there a formula that can calculate years, months and days into a decimal?
For example: 23 years, 6 months, 0 days would be 23.5 years.

 

[JLatham]

Depends on how your years, months and days are stored.

Assuming they're in cells A1 (years), B1 (months) and C1 (days), then this
formula would get you close:
=A1+((B1*30)+C1)/365.25
That will actually return 23.49281, which if you round to 1 decimal place is
23.5

If you want to gty to get closer use B1*30.4375 instead of B1*30, since
365.25/12 = 30.4375 which you can "assume" is the average number of days in
any single month.

 

[T. Valko]

For example: 23 years, 6 months, 0 days

would be 23.5 years.

I assume the days will be less than "a months worth of days". You won't have
something like this:

23 years, 6 months, 72 days

So, how many days are in a month? 28, 29, 30 or 31?

What result would you expect from this:

23 years, 6 months, 29 days

Is this: 23 years, 6 months, 0 days, all in one cell?

How about posting several representative samples along with the results you
expect. As you can see there are a lot of details that need to be
considered!

 

[Mike]

Thanks!!

Depends on how your years, months and days are stored.

Assuming they're in cells A1 (years), B1 (months) and C1 (days), then this
formula would get you close:
=A1+((B1*30)+C1)/365.25
That will actually return 23.49281, which if you round to 1 decimal place is
23.5

If you want to gty to get closer use B1*30.4375 instead of B1*30, since
365.25/12 = 30.4375 which you can "assume" is the average number of days in
any single month.

 

[David Biddulph]

An answer to the month length problem might be to change your formula to
=A1+B1/12+C1/365.25

You might change the 365.25 to 365.2425 too.

But of course it depends what the OP really wants.