Excel EDATE Function

of 01

Add/Subtract Months to Dates

Using the EDATE Function to Add and Subtract Months to a Date in Excel
Using the EDATE Function to Add and Subtract Months to a Date. © Ted French

EDATE Function Overview

Excel's EDATE function can be used to quickly add or subtract months to known dates - such as the maturity or due dates of investments or the start or end dates of projects.

Since the function only adds or subtracts entire months to a date, the result will always fall on the same day of the month as the starting date.

Serial Numbers

The data returned by the EDATE function is a serial number or serial date. Apply date formatting to cells containing the EDATE function in order to display legible dates in the worksheet - outlined below.

The EDATE Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the EDATE function is:

= EDATE ( Start_date , Months )

Start_date - (required) the start date of the project or time period in question

  • this argument can be a date entered into the function or a cell reference to the location of the data in the worksheet

Months - (required) - the number of months before or after the Start_date

  • this argument can be a value entered into the function or a cell reference to the location of the data in the worksheet
  • positive values yield future dates - row 8 in the image above
  • negative values past dates - row 3 in the image above
  • If months is not an integer, it is truncated (the decimal portion is removed) - as shown in row 8 in the image above where 12.25 years is truncated to the integer 12

#VALUE! Error Value

If the Start_date argument is not a valid date, the function returns the #VALUE! error value - as shown in row 4 in the image above, since 2/30/2016 (February 30, 2016) is invalid

Excel's EDATE Function Example

As shown in the image above, this example uses the EDATE function to add and subtract a various number of months to the date January 1, 2016.

The information below covers the steps used to enter the function into cells B3 and C3 of the worksheet.

Entering the EDATE Function

Options for entering the function and its arguments include:

  • Typing the complete function: =EDATE($A$3,C2) into cell C3;
  • Selecting the function and its arguments using the EDATE function dialog box.

Although it is possible to just type the complete function in by hand, many people find it easier to use the dialog box to enter a function's arguments.

The steps below cover entering the EDATE function shown in cell B3 in the image above using the function's dialog box.

Since the values to be entered for the Months argument are negative (-6 and -12) the dates in cells B3 and C3 will be earlier than the start date.

 EDATE Example - Subtracting Months

  1. Click on cell B3 - to make it the active cell;
  2. Click on the Formulas tab of the ribbon;
  3. Click on Date and Time functions to open the function drop down list;
  4. Click on EDATE in the list to bring up the function's dialog box;
  5. Click on the Start_date line in the dialog box;
  6. Click on cell A3 in the worksheet to enter that cell reference into the dialog box as the Start_date argument;
  7. Press the F4 key on the keyboard to make A3 an absolute cell reference - $A$3;
  8. Click on the Months line in the dialog box;
  9. Click on cell B2 in the worksheet to enter that cell reference into the dialog box as the Months argument;
  10. Click OK to complete the function and return to the worksheet;
  11. The date 7/1/2015 (July 1, 2015) - appears in cell B3 which is six months prior the start date;
  12. Use the fill handle to copy the EDATE function to cell C3 - the date 1/1/2015 (January 1, 2015) should appear in cell C3 which is 12 months prior to the start date;
  13. If you click on cell C3 the complete function =EDATE($A$3,C2) appears in the formula bar above the worksheet;

Note: If a number, such as 42186, appears in cell B3 it's likely that the cell has General formatting applied to it. See the instructions below for change the cell to date formatting;

Changing the Date Format in Excel

A quick and easy way to change the date format for cells containing the EDATE function is to choose one from the  list of pre-set formatting options in the Format Cells dialog box. The steps below use the keyboard shortcut combination of Ctrl + 1 (number one) to open the Format Cells dialog box.

To change to a date format:

  1. Highlight the cells in the worksheet that contain or will contain dates
  2. Press the Ctrl + 1 keys to open the Format Cells dialog box
  3. Click on the Number tab in the dialog box
  4. Click on Date in the Category list window (left side of the dialog box)
  5. In the Type window (right side), click on a desired date format
  6. If the selected cells contain data, the Sample box will display a preview of the selected format
  7. Click the OK button to save the format change and close the dialog box

For those who prefer to use the mouse rather than the keyboard, an alternate method for opening the dialog box is to:

  1. Right click the selected cells to open the context menu
  2. Choose Format Cells… from the menu to open the Format Cells dialog box


If, after changing to a date format for a cell, the cell displays a row of hash tags, it is because the cell is not wide enough to display the formatted data. Widening the cell will correct the problem.

mla apa chicago
Your Citation
French, Ted. "Excel EDATE Function." ThoughtCo, May. 17, 2017, thoughtco.com/excel-edate-function-4063494. French, Ted. (2017, May 17). Excel EDATE Function. Retrieved from https://www.thoughtco.com/excel-edate-function-4063494 French, Ted. "Excel EDATE Function." ThoughtCo. https://www.thoughtco.com/excel-edate-function-4063494 (accessed January 18, 2018).