A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

How do you add 18 years to a date in Excel?



 
 
Thread Tools Display Modes
  #1  
Old October 18th 06, 05:30 PM posted to microsoft.public.excel.misc
Janelle Lister
external usenet poster
 
Posts: 4
Default 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?
Ads
  #2  
Old October 18th 06, 05:33 PM posted to microsoft.public.excel.misc
Kevin B
external usenet poster
 
Posts: 1,317
Default 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?

  #3  
Old October 18th 06, 05:39 PM posted to microsoft.public.excel.misc
Vera Lemon
external usenet poster
 
Posts: 11
Default 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?

  #4  
Old October 18th 06, 05:58 PM posted to microsoft.public.excel.misc
Dave F
external usenet poster
 
Posts: 2,574
Default 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?

  #5  
Old October 19th 06, 03:58 PM posted to microsoft.public.excel.misc
hot dogs
external usenet poster
 
Posts: 54
Default 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?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel date format issue rs-excel Excel Discussion (Misc queries) 1 October 17th 06 11:37 PM
Incorrect Excel date 1900 is not a leap year 1/1/1901 <> 367 faijaimond Excel Discussion (Misc queries) 2 October 3rd 06 12:44 AM
To create formula to add 3 years and subtract 1 day from a date? rostroncarlyle Excel Worksheet Functions 2 December 8th 05 11:21 PM
Excel file modification date GROSNER Excel Discussion (Misc queries) 5 March 4th 05 01:19 AM
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 1 January 5th 05 08:36 AM


All times are GMT +1. The time now is 02:38 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.