Advancing the year

  • Thread starter Thread starter JT
  • Start date Start date
J

JT

I have a date in C3 (October 12, 2009) and would like cell d3 to display that
same day, but 3 years later (October 12, 2012). Is there a formula that I
can use to make that happen?

Thanks!

JT
 
JT said:
I have a date in C3 (October 12, 2009) and would like cell d3 to display
that
same day, but 3 years later (October 12, 2012). Is there a formula that I
can use to make that happen?
Click to expand...

Ostensibly:

=DATE(3+YEAR(C3),MONTH(C3),DAY(C3))

But beware of Feb 29 in leap years. A better solution might be:

=EDATE(C3,12*3)

If you get a #NAME error, see the EDATE help page. If you cannot load the
Analysis ToolPak, then try:

=DATE(3+YEAR(C3),1+MONTH(C3),0)
 
I have a date in C3 (October 12, 2009) and would like cell d3 to
display that same day, but 3 years later (October 12, 2012). Is there
a formula that I can use to make that happen?
Click to expand...

One way:
=DATE(YEAR(C3)+3,MONTH(C3),DAY(C3))

Notice that for 02-29-08 (leap year) the result is 03-01-11.
 
Try one of these:

=DATE(YEAR(C3)+3,MONTH(C3),DAY(C3))

=EDATE(C3,36)
Format as Date

There is a difference in the way these formulas calculate the ending date.

If C3 contained the date 2/29/2008 the first formula will return 3/1/2011.
The second formula will return 2/28/2011. There is no 29th day in February
in non-leap years and both formulas handle this differently as you can see.

The EDATE function requires the Analysis ToolPak add-in be installed for
Excel versions prior to Excel 2007. If you use the EDATE function and get a
result of #NAME? look in Excel help for the EDATE function and it'll tell
you how to correct that.
 
Errata....
If you cannot load the Analysis ToolPak, then try:
=DATE(3+YEAR(C3),1+MONTH(C3),0)
Click to expand...

On second thought, do __not__ try that ;). It's wrong!


----- original message -----
 
Back
Top