What is the EDATE function?
The Excel EDATE function returns a date on the same day of the month, some months in the future or even the past. The function can be used to calculate maturity dates, expiration dates, and other due dates. To get a date in the future, use a positive value for months, and a negative value to get dates in the past.
- date – this represents the date to which months would be added.
- months – The number of months to be added to the date. The value could be in form of a number itself or a cell containing a number.
The EDATE function is completely automatic. All you have to do is supply a valid date and a number of months and the function will return a new date.
How to use the EDATE function to add months to date
The spreadsheet in this example contains 3 dates to which we would be adding months to. First, let us add 7 months to the 12th of October, 2017.
The following steps should be taken;
- Click on the cell where the new date would be displayed (C4).
Figure 1. Click on the cell
- Insert the formula; =EDATE(A4,B4) into the cell. Cell A4 contains the date (12 October 2017), cell B4 contains the number of months to be added (7).
Figure 2. Insert formula
- Press Enter on your keyboard. The result of adding 7 months to “12 October 2017” should be “12 May 2018”.
Figure 3. Press ENTER
- Next up, we add 15 months to “5 December 2016”. Insert the formula =EDATE(A5,B5) and press Enter. The result should be “5 March 2018”
Cell A5 contains the date (5 December 2016), cell B5 contains the number of months to be added (15).
Figure 4. Add 15 months
- Lastly, let us try going to the past by using a negative value. For cell C6, insert the formula =EDATE(A6,B6) and Press Enter. The result is “9 August 2017”.
Cell A6 contains the date (9 May 2018), cell B6 contains the number of months to be subtracted (9).
Figure 5. Subtract 9 months
- Instead of storing the months to be added into a cell, you can use that number directly in the EDATE function. For example, =EDATE(A4,7) – this would add 7 months to 12 October which is in cell A4.
- Don’t forget to format the cells where you want to input or display your date. To do this click on the cell(s). Then, Right click and go to Format cells>Number>Date. Then choose your preferred date formats.
- To add months to a date, supply a positive value as months.
- To subtract months from a date, supply a negative value as months.