Year/Month/Date Question

  • Thread starter Thread starter Mike
  • Start date Start date
M

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.
 
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.
 
For example: 23 years, 6 months, 0 days
would be 23.5 years.
Click to expand...

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!
 
Thanks!!

JLatham said:
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.
Click to expand...
 
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.
 
Back
Top