JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,676
- Office Version
- 365
- Platform
- 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:
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:
[TABLE="width: 350"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Jan 01, 2000[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Jan 01, 2001[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Jul 04, 1976[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Jul 04, 1986[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 28, 2004[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Feb 28, 2005[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 29, 2004[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mar 01, 2005[/TD]
[TD="align: center"]Incorrect[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 29, 2004[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Feb 29, 2008[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
</tbody>[/TABLE]
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.
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:
[TABLE="width: 350"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]Jan 01, 2000[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Jan 01, 2001[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Jul 04, 1976[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Jul 04, 1986[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 28, 2004[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Feb 28, 2005[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 29, 2004[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Mar 01, 2005[/TD]
[TD="align: center"]Incorrect[/TD]
[/TR]
[TR]
[TD="align: center"]Feb 29, 2004[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Feb 29, 2008[/TD]
[TD="align: center"]Correct[/TD]
[/TR]
</tbody>[/TABLE]
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.