The EDATE function in Excel quickly adds or subtracts months from known dates. For example, the EDATE function can be used to determine the timing or timing of investments or project start or end dates.
Remark . The information in this 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 from dates
- Syntax and arguments of the EDATE function
- #MEANING! Error value
- EDATE Function example in Excel
- Enter the EDATE function
- EDATE example – subtract months
- Change date format in Excel
- Troubleshoot some hashtags
Add or subtract months from dates
Because the function only adds or subtracts whole months from a date, the result always falls on the same day of the month as the start date.
The data returned by the EDATE function is either a serial number or a serial date. To display readable dates in the correct date format, apply date format to cells that contain the EDATE function.
Syntax and arguments of the EDATE function
Function syntax refers to the layout of the function and includes the function name, parentheses, and arguments.
Syntax for the EDATE function:
= DATE (month_start_date)
Start_date (mandatory): The start date of the project, or the period concerned.
This argument can be a date entered in the function, or a cell reference to find the data on the worksheet.
Months (mandatory): number of months before or after starting date.
This argument can be a value entered into the function, or a cell reference to find the data on the worksheet.
Positive values indicate future dates (line 8 in the image above).
Negative values indicate past dates (line 3 in the image above).
If month is not an integer, it is truncated (the decimal part is removed), as shown in line 8 in the image above, where 12.25 years is truncated to the integer 12.
#MEANING! Error value
If the argument Starting date is not a valid date, the function returns an error value #WHERE THE! (as shown in line 4 in the figure above) from 02/30/2016 (February 30, 2016) is no longer valid.
EDATE Function example in Excel
As seen in the image above, this example uses the EDATE function to add and subtract different numbers of months up to the date January 1, 2016.
The information below covers the steps used to enter a function in cells B3 and C3 of a worksheet.
Enter the EDATE function
Input options for a function and its arguments include:
Full function input: =EDATE($A$3,C2) to cell C3;
Selecting a function and its arguments using the EDATE function dialog box.
While you can just manually enter the entire function, many people find it easier to use the dialog box to enter the function’s arguments.
The steps below are about entering the EDATE function shown in cell B3 in the image above using the function’s dialog box.
Because the values entered for the argument Months are negative (-6 and -12), the dates in cells B3 and C3 will be earlier than the start date.
EDATE example – subtract months
Select cell B3 to make it active.
Select formulas . In Excel Online, click Insert function next to the formula bar to open the Insert Function dialog box.
Select Date and time functions to open the drop-down list of functions. Select in Excel Online date and time in the Select category list.
Select EDIT in the list to open the function’s dialog box.
Select line Starting date in the dialog box.
Select cell A3 on the worksheet to enter a reference to that cell in the dialog box as the Start date argument.
Click F4 to make A3 an absolute cell reference ($A$3).
Select line Months in the dialog box.
Select cell B2 on a sheet to enter a reference to that cell in the dialog box as an argument Months .
Select Okay to end the function and return to the worksheet.
The date 01/07/2015 (July 1, 2015) appears in cell B3, which is six months before the start date.
Use the fill handle to copy the EDATE function to cell C3. The date 01/01/2015 (January 1, 2015) appears in cell C3, which is 12 months before the start date.
Select cell C3 to display the full function =EDATE($A$3,C2) in the formula bar above the worksheet.
Remark . For example, if there is a number in cell B3 42186 the cell probably has formatting applied Occur often . To apply the date format to a cell, see the instructions below.
Change date format in Excel
A quick and easy way to change the date format for cells that contain the EDATE function is to select one from a list of predefined formatting options in the Format Cells dialog box.
The steps below use the hotkey ctrl + a or order + a (number one). Format Cells dialog box. (This shortcut is not available in Excel Online.)
To change the date format:
Select the cells on the worksheet that contain or will contain dates.
Click ctrl + a or order + a to open the Format Cells dialog box.
Select tab Number in the dialog box.
Select date in the category list (on the left side of the dialog box).
In the Type . window (right) select the desired date format.
If the selected cells contain data, the preview pane shows a preview of the selected format.
Select Okay to save the resizing and close the dialog box.
If you prefer to use the mouse over the keyboard, an alternative way to open the dialog is:
Right click on the selected cells to open the context menu.
Select Cell format to open the dialog Cell format.
Troubleshoot some hashtags
If the hashtag string appears in the cell after the date format has been changed for a cell, it’s because the cell isn’t wide enough to display the formatted data. Cell expansion solves the problem.