Excel and Office RAQ

Subtracting or adding whole years with leap year dates in Excel


Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing


Knowledge measurement audit

Item bank services

Item design course








Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in


Home page

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:


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:


If instead you want to add (rather than subtract) a whole year reliably, then the formula is:




Back to other RAQ topics