Excel EDATE Function

Add or subtract dates in Excel with the EDATES function

 rawpixel \ Unsplash

Excel's EDATE function quickly adds or subtracts months to known dates. The EDATE function can be used, for example, to determine the maturity or due dates of investments or the start or end dates of projects.

Note The information in the article applies to Excel 2019, 2016, 2013, and 2010; Excel Online; and Excel 2019 for Mac, Excel 2016 for Mac, and Excel for Mac 2011.

Add or Subtract Months to Dates

Using the EDATE Function to Add and Subtract Months to a Date in Excel

 

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

The data returned by the EDATE function is a serial number or serial date. To display legible dates that are in the proper date format, you'll apply date formatting to cells containing the EDATE function.

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 yield 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.

An Example of Excel's EDATE Function

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.

Enter 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 - Subtract Months

  1. Select cell B3 to make it the active cell.
  2. Select Formulas. In Excel Online, select the Insert Function button next to the formula bar to open the Insert Function dialog box.
  3. Select Date and Time Functions to open the function drop-down list. In Excel Online, choose Date and Time in the Pick a Category list. 
  4. Select EDATE in the list to bring up the function's dialog box.
  5. Select the Start_date line in the dialog box.
  6. Select cell A3 in the worksheet to enter that cell reference into the dialog box as the Start_date argument.
  7. Press F4 to make A3 an absolute cell reference ($A$3).
  8. Select the Months line in the dialog box.
  9. Select cell B2 in the worksheet to enter that cell reference into the dialog box as the Months argument.
  10. Select 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 to the start date.
  12. Use the fill handle to copy the EDATE function to cell C3. The date 1/1/2015 (January 1, 2015) appears in cell C3 which is 12 months prior to the start date.
  13. Select cell C3 to display the complete function =EDATE($A$3,C2) 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 to apply the Date format to the cell.

Change 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 or Command+1 (number one) to open the Format Cells dialog box. (This shortcut is not available in Excel Online.)

To change to a date format:

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

If you 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 to open the Format Cells dialog box.

Troubleshoot a Row of Hashtags

Why are there hashtags in Excel?

 

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