The EDATE function takes a starting date and a number as its input. It then increments (or decrements) the date by the number of months that you specify.
This formula can be helpful because not all months have the same number of days. Additionally, February has a different number of days depending on the year. The EDATE function accounts for all of these differences. If the number of months you provide has decimals, it is truncated instead of rounded so keep it as a whole number.
Dates require special care when used in formulas as they don’t behave as regular numbers do in spreadsheets.
The EDATE function adds or subtracts a given number of months to a given date.
=EDATE(start date,number of months)
start date– The starting date from which the calculation begins. Remember how dates work in formulas so that you know how to enter this date.
number of months– The number of months by which to increase/decrease the starting month
Example 1 – In the Same Month
Firstly, below is a simple explanation of the EDATE function.
|Add 13 months to 5/17/2017||6/17/2018|
Subtract two months from 5/17/2017
The EDATE function can go forward and backward in time.
Example 2 – Knowing the Number of Days in a Month
Secondly, this example shows how the EDATE function knows the number of days each month. Consequently, knowing the number of days takes away the chore of manually adding the days.
Note that the second function is
DATE and not
|Add three months to 5/17/2017||8/17/2017|
Add three months to 5/17/2017
Note how much easier it is to use the EDATE function.
Example 3 – Days in a Leap Year
Lastly, the EDATE function knows the number of days in a leap year. Note the two dates highlighted below that are one day different.
|Add 1 month to 2/17/2015||3/17/2015|
|Add 1 month to 2/17/2016||3/17/2016|
|Add 28 days to 2/17/2015||3/17/2015|
Add 28 days to 2/17/2016
Live Examples in Sheets
Go to this spreadsheet for the examples of the EDATE function shown above that you can study and use anywhere you would like.