Excel and Office
RAQ 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: =D1365(DAY(D1)<>DAY(D1365)) If instead you want to add (rather than subtract) a whole year reliably, then the formula is: =D1365+(DAY(D1)<>DAY(D1365))

