Monthly and Yearly Date Arithmetic with Edate() in Excel
Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.
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.
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() is simple, give it a date and a number of months, it will return the adjusted date.
Edate(<excel date>, number of months)
The number of months must be an integer. Any fraction/decimal is ignored.
To subtract months, use a negative number eg =Edate(A8,-3)
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.
Excel returns the last day of the month.
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.
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().
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().
Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.