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
|
|||
|
|||
Year/Month/Date Question
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. |
#2
|
|||
|
|||
Year/Month/Date Question
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. "Mike" wrote: 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. |
#3
|
|||
|
|||
Year/Month/Date Question
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! -- Biff Microsoft Excel MVP "Mike" wrote in message ... 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. |
#4
|
|||
|
|||
Year/Month/Date Question
Thanks!!
"JLatham" wrote: 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. "Mike" wrote: 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. |
#5
|
|||
|
|||
Year/Month/Date Question
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. -- David Biddulph "JLatham" wrote in message ... 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. "Mike" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|