# Caculating a date X years in the future

#### JenniferMurphy

##### Well-known Member
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:
 A B C D Jan 01, 2000 1 Jan 01, 2001 Correct Jul 04, 1976 10 Jul 04, 1986 Correct Feb 28, 2004 1 Feb 28, 2005 Correct Feb 29, 2004 1 Mar 01, 2005 Incorrect Feb 29, 2004 4 Feb 29, 2008 Correct

<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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try
Code:
``=EDATE(A1,12*B1)``

Last edited:
Wow, that works perfectly. Thanks so much.

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)))

Thanks, same solution as Marcel (I assume you meant "," rather than ";" )

Why would anyone ever use the long version?

Replies
4
Views
418
Replies
2
Views
379
Replies
1
Views
244
Replies
16
Views
2K
Replies
1
Views
63

1,218,909
Messages
6,145,166
Members
450,592
Latest member
Boulder127

### 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.

### Which adblocker are you using?

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

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