Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 18th 06, 05:30 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
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?

  #2   Report Post  
Old October 18th 06, 05:33 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 1,316
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   Report Post  
Old October 18th 06, 05:39 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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   Report Post  
Old October 18th 06, 05:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2006
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   Report Post  
Old October 19th 06, 03:58 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
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?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 01:12 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017