Subtracting or adding whole years with leap year dates in Excel
Q: In my job I deal with fiscal periods very often. I have a problem that is related to calculations involving leap years. For example, I may enter a date in cell D1, and then in cell D7 I enter a formula:
=DATE(year(D1)-1,month(D1),day(D1))
This is to calculate the date for one year before the current date in cell D1. This formula works fine, except for the month of February in leap years. When I enter 2/29/00 at cell D1, D7 became 3/1/99 instead of 2/28/99 and when I enter 2/28/01 at D1, D7 became 2/28/00 instead of 2/29/00. Perhaps I am using the wrong formula; I need the formula to know that when I enter the last day of the month in cell D1, that I need the last day of the month in cell D7. How can I do this?
A: Here is a formula which does exactly what you need:
=D1-365-(DAY(D1)<>DAY(D1-365))
If instead you want to add (rather than subtract) a whole year reliably, then the formula is:
=D1-365+(DAY(D1)<>DAY(D1-365))