Caculating a date X years in the future

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
I need to calculate the date that is X years in the future from some base date. If the base date is 1/01/2000 and X=1, then the future date would be 1/01/2001.

If the base date is in A1 and X is in B1, then I put this code in C1:
Code:
=DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1))

This appeared to work until the base date happened to be a leap day (Feb 29). In that case, the formula returns either Feb 29 or Mar 1 depending on whether the target year is also a leap year or not.

Here are some examples:
ABCD
Jan 01, 20001Jan 01, 2001Correct
Jul 04, 197610Jul 04, 1986Correct
Feb 28, 20041Feb 28, 2005Correct
Feb 29, 20041Mar 01, 2005Incorrect
Feb 29, 20044Feb 29, 2008Correct

<tbody>
</tbody>

I can't really fault Excel. There isn't a Feb 29 in most years. But I think Feb 28 is a better anniversary date than Mar 1 for Feb 29.

Is there a better way to do this? Or do I need to write a UDF to test for the existence of Feb 29 in the target year?

I'm going to start a movement to abolish Feb 29. We'll move it to the end of the year and make it a holiday. It will not be part of any month and no one will be allowed to have an anniversary on that date. While we're at it, we'll change all of the months to 30 days and have 5 or 6 extra days at the end of the year. :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try using this, if you using office 2007 and above or if you on 2003 make sure install Analysis Tool Pack Add Ins...

=EDATE(A1;B1*12) and dragged down as necessary

or long version

=IF(MONTH(A1)=2,DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)-1),DATE(YEAR(A1)+B1,MONTH(A1),DAY(A1)))
 
Upvote 0
Thanks, same solution as Marcel (I assume you meant "," rather than ";" :))

Why would anyone ever use the long version?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top