Monthly and Yearly Date Arithmetic with Edate() in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Adding or subtracting months in Excel is easier with the Edate() function.

As you probably know, Excel uses whole numbers to calculate dates.  Add 1 to an Excel date adds a day.

monthly and yearly date arithmetic with edate in excel 24252 - Monthly and Yearly Date Arithmetic with Edate() in Excel

How to add a whole month?

How to make Excel jump a whole calendar month?  For example from 1 January to 1 February ?

A month can be 28, 29, 30 or 31 days, so adding days won’t work.

The solution is the Edate() function that’s been in Excel since at least Excel 2007 for Windows and Excel 2011 for Mac.

Edate()

Edate() is simple, give it a date and a number of months, it will return the adjusted date.

Edate(<excel date>, number of months)

monthly and yearly date arithmetic with edate in excel microsoft excel 24256 - Monthly and Yearly Date Arithmetic with Edate() in Excel

The number of months must be an integer. Any fraction/decimal is ignored.

Subtract months

To subtract months, use a negative number eg =Edate(A8,-3)

monthly and yearly date arithmetic with edate in excel microsoft excel 24257 - Monthly and Yearly Date Arithmetic with Edate() in Excel

Mismatched Months

What happens when you add to the last day of a month with 31 days and the result is a month with 30 days? For example, adding a month to 31 August can’t be 31 September because the ninth month only has 30 days.

monthly and yearly date arithmetic with edate in excel microsoft excel 24258 - Monthly and Yearly Date Arithmetic with Edate() in Excel

Excel returns the last day of the month.

Leap Years

It’s the same with leap years.  Excel is smart enough to cope with leap years but it might not give the result you want.

If you try adding a month to 30 Jan 2016 (a leap year) it can’t be 30 Feb 2016.

Adding a month to 29th or 30th January in a leap year gives you the same date … the 29th Feb.

monthly and yearly date arithmetic with edate in excel microsoft excel 24259 - Monthly and Yearly Date Arithmetic with Edate() in Excel

That’s OK but might not be the result you want.  Some organizations try to avoid the leap year complication and deem anything on 29th Feb to be on either 28th Feb or 1st March.

Adding Years with Edate()

There’s no equivalent of Edate() for adding or subtracting years but it’s easy to workaround it.

Just add or subtract 12, 24, 36 months using Edate().

monthly and yearly date arithmetic with edate in excel microsoft excel 24260 - Monthly and Yearly Date Arithmetic with Edate() in Excel

As you can see, adding a year to 29th Feb in a leap year returns the 28th Feb in the following, non-leap, year.   If the cell value you have is in years, just multiply by 12 in Edate().

subs profile e1563205311409 - Monthly and Yearly Date Arithmetic with Edate() in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address