Excel and Office RAQ

Subtracting or adding whole years with leap year dates in Excel

   

Tactical solutions

Research projects

Microsoft Office

Multiple choice guessing

Services

Knowledge measurement audit

Item bank services

Item design course

Products

WaterMarker

iOTA

STASiS

ACiS

CaSelector

CaSCADE

Technical articles

Excel and Office RAQ

Free Excel Add-in

Free PowerPoint Add-in

Contact

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:

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

 

 

Back to other RAQ topics