How do you add 18 years to a date in Excel?
I'm trying to calculate the date of a person's 18th birthday using a formula
in Excel. For example, if the birthdate is January 1, 2000 the age of majority is January 1, 2018. What formula can I enter to have Excel calculate the date for me? |
How do you add 18 years to a date in Excel?
If your date is in cell A1 this formula will do it for you:
=DATE(YEAR(A1)+18,MONTH(A1),DAY(A1)) -- Kevin Backmann "Janelle Lister" wrote: I'm trying to calculate the date of a person's 18th birthday using a formula in Excel. For example, if the birthdate is January 1, 2000 the age of majority is January 1, 2018. What formula can I enter to have Excel calculate the date for me? |
How do you add 18 years to a date in Excel?
This may be the long way, however, it worked for me...try this formula:
=C3+(18*365+4) C3 would be the cell where your birthdate is. I multiplied 18 years times 365 days and had to add 4 because leap year occurs 4 times in an 18 year period. "Janelle Lister" wrote: I'm trying to calculate the date of a person's 18th birthday using a formula in Excel. For example, if the birthdate is January 1, 2000 the age of majority is January 1, 2018. What formula can I enter to have Excel calculate the date for me? |
How do you add 18 years to a date in Excel?
If you want to avoid determining how many leap years are in the period you
could use: =C3+(18*365+(round(18/4,0)) Dave -- Brevity is the soul of wit. "Vera Lemon" wrote: This may be the long way, however, it worked for me...try this formula: =C3+(18*365+4) C3 would be the cell where your birthdate is. I multiplied 18 years times 365 days and had to add 4 because leap year occurs 4 times in an 18 year period. "Janelle Lister" wrote: I'm trying to calculate the date of a person's 18th birthday using a formula in Excel. For example, if the birthdate is January 1, 2000 the age of majority is January 1, 2018. What formula can I enter to have Excel calculate the date for me? |
How do you add 18 years to a date in Excel?
Thats an interseting one, if you start your calculation of 18 years in the
future from the year 2000, and if a leap year occurs in the year 2001, then you would have an extra day for the years; 2001,2005,2009,2013,2017 so you would have to add 5 extra days. But if the leap year occured in the year 2003 then you would add an extra day for the years; 2003,2007,2011,2015 so you would only add 4 days. how could you determine where the leap year is? I think the only way is to use Kevin B's method. As this uses the excel date system which takes leap years into account. "Dave F" wrote: If you want to avoid determining how many leap years are in the period you could use: =C3+(18*365+(round(18/4,0)) Dave -- Brevity is the soul of wit. "Vera Lemon" wrote: This may be the long way, however, it worked for me...try this formula: =C3+(18*365+4) C3 would be the cell where your birthdate is. I multiplied 18 years times 365 days and had to add 4 because leap year occurs 4 times in an 18 year period. "Janelle Lister" wrote: I'm trying to calculate the date of a person's 18th birthday using a formula in Excel. For example, if the birthdate is January 1, 2000 the age of majority is January 1, 2018. What formula can I enter to have Excel calculate the date for me? |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com