This article covers how to use the EDATE function in Excel for Microsoft 365, Excel 2019, and Excel 2016 and earlier.

What to Know

  • The syntax for EDATE has two arguments: start_date and months and is written =EDATE(date,months).You can use positive or negative integers to indicate the number of months to be calculated.The EDATE function is commonly used to calculate maturity dates in financial transactions, expiration dates, and due dates.

How to Use the EDATE Function in Microsoft Excel

The EDATE function in Microsoft Excel calculates a date after adding or subtracting a specified number of months to a given date. For example if you want to know the date 13 months from today, you could use the EDATE function to find that.

The function relies on two arguments:

  • start_date: This is the date on which you want the return date based.month: This is the number of months you want to add or subtract from the start_date.

The syntax for the EDATE function is:

=EDATE(date,months)

In this formula, the date is the location of the date in Excel, and the month is the number of months you want to add or subtract.

Using the example above, then, the function might look like this:

(Assuming that the date for today is 10/18/2020)

=EDATE(cell,13)

There are a few caveats to how this works, however, so follow these step-by-step instructions:

  • In Excel, type the date you want to begin with into a cell. In this example, the date is in cell A2, and is 2020-10-18.
  • With Excel, it’s not as simple as typing a date and assuming Excel will recognize it. You actually have to format the date you would like as a Date using the Format menu. To do that, type the date, then select the cell (you can also select multiple cells for format). Then, press Ctrl+1 on you keyboard. This opens the Format menu. Select the Date tab and select the format you would like to use for the date.
  • In the next column (labeled Months in this example), on the same line, type the number of months you would like to use. Remember this can be a number of months to add or subtract, so it can be a whole number or a negative number, but it cannot be a decimal number.
  • In the next column (labeled EDATE in this example), on the same line type the formula:
  • =EDATE(A2,13)
  • This tells Excel that you want to add 13 months to the date in cell A2.
  • Now, press Enter on your keyboard. This will return a number that’s not a date. Don’t panic. Microsoft Excel stores dates as sequential numbers, starting from January 1, 1900. So, if you entered the formula correctly, the number returned should be 44518. That’s because the October 18, 2020 date + 13 months is 44,518 days after January 1, 1900.
  • Excel recognizes end of the month dates and will move them backward or forward to adjust for the end of an odd month. For example, if in this example, the start date was January 31, 2020 and we still wanted to add 13 months to it, the result of the EDATE function would be February 28, 2021.
  • To convert the returned number into a recognizable date, click the cell then press Ctrl+1 on your keyboard. This opens the Format Cells dialog box.
  • In the Format Cells dialog box, select the Date tab in the category box on the left.
  • From the options that appear on the right, select the format of the date you would like to use and then click OK.
  • The date should now be displayed in your chosen format.

Uses of the EDATE Function

Most often, the EDATE function, which is categorized under Excel’s DATE/TIME Functions, is used in accounts payable or accounts receivable functions to calculate the maturity dates for accounts. However, you can also use this function when determining counts by month, or even just to figure out what a date might be X number of months from a give date or X number of months before a given date.

In this example, the Excel spreadsheet has 3 columns: Start Date, Months, and EDATE. These are the columns that will be used to illustrate how this function works. You do not have to format you spreadsheet the same way. What’s important is the Start Date is formatted correctly, and the formula is written correctly. You can return the result in any cell.

In Excel, type the date you want to begin with into a cell. In this example, the date is in cell A2, and is 2020-10-18.

With Excel, it’s not as simple as typing a date and assuming Excel will recognize it. You actually have to format the date you would like as a Date using the Format menu. To do that, type the date, then select the cell (you can also select multiple cells for format). Then, press Ctrl+1 on you keyboard. This opens the Format menu. Select the Date tab and select the format you would like to use for the date.

In the next column (labeled Months in this example), on the same line, type the number of months you would like to use. Remember this can be a number of months to add or subtract, so it can be a whole number or a negative number, but it cannot be a decimal number.

In the next column (labeled EDATE in this example), on the same line type the formula:

=EDATE(A2,13)

This tells Excel that you want to add 13 months to the date in cell A2.

Now, press Enter on your keyboard. This will return a number that’s not a date. Don’t panic. Microsoft Excel stores dates as sequential numbers, starting from January 1, 1900. So, if you entered the formula correctly, the number returned should be 44518. That’s because the October 18, 2020 date + 13 months is 44,518 days after January 1, 1900.

Excel recognizes end of the month dates and will move them backward or forward to adjust for the end of an odd month. For example, if in this example, the start date was January 31, 2020 and we still wanted to add 13 months to it, the result of the EDATE function would be February 28, 2021.

To convert the returned number into a recognizable date, click the cell then press Ctrl+1 on your keyboard. This opens the Format Cells dialog box.

In the Format Cells dialog box, select the Date tab in the category box on the left.

From the options that appear on the right, select the format of the date you would like to use and then click OK.

The date should now be displayed in your chosen format.

The EDATE function may also be used with other functions.

Get the Latest Tech News Delivered Every Day