Summary

The Excel EDATE function returns a date on the same day of the month, n months before or after a start date. You can use EDATE to calculate expiration dates, maturity dates, and other due dates derived from a start date.

Purpose 

Get date n months in future or past

Return value 

New date as Excel serial number

Syntax

=EDATE(start_date,months)
  • start_date - Start date as a valid Excel date.
  • months - Number of months before or after start_date.

How to use 

The EDATE function returns a date on the same day of the month, n months before or after a start date. You can use EDATE to generate expiration dates, contract dates, due dates, anniversary dates, retirement dates, and other dates derived from a start date. EDATE returns a serial number corresponding to a date. To display the result as a date, apply a number format of your choice.

The EDATE function takes two arguments, start_date and months:

  • start_date - a valid Excel date to use as the starting point.
  • months - a whole number that specifies how many months to move. Use a positive number of months to get a date in the future, and a negative number for a date in the past.

Note: The EDATE function returns the same day of the month. If you want to get the last day of a month n months in the past or future, use the EOMONTH function.

The EDATE function explained

With a given start date EDATE returns a new date by adding the number of months provided. To illustrate how EDATE works, assume we want to create dates for the first day of each quarter, starting from January 1, 2024. We can do this with the following formulas:

=EDATE("1-Jan-2024",0) // returns 1-Jan-2024
=EDATE("1-Jan-2024",3) // returns 1-Apr-2024
=EDATE("1-Jan-2024",6) // returns 1-Jul-2024
=EDATE("1-Jan-2024",9) // returns 1-Oct-2024

The first formula does not change the date since months is zero. The second formula adds 3 months, the third formula adds 6 months, and the fourth formula adds 9 months to the date. In all cases, EDATE returns the 1st of the month, since the day is 1 in the start date. Of course, in most real-life scenarios, you will not hardcode dates into formulas like this. You will instead use cell references. If we enter the date January 1, 2024, in cell A1, the same formulas look like this:

=EDATE(A1,0) // returns 1-Jan-2024
=EDATE(A1,3) // returns 1-Apr-2024
=EDATE(A1,6) // returns 1-Jul-2024
=EDATE(A1,9) // returns 1-Oct-2024

The results are the same. And if the date in A1 is changed, EDATE will generate new dates. You can use negative numbers for months to create dates before the start date. With the same date in A1, the formulas below return dates that are 3, 6, 9, and 12 months before January 1, 2024:

=EDATE(A1,-3) // returns 1-Oct-2023
=EDATE(A1,-6) // returns 1-Jul-2023
=EDATE(A1,-9) // returns 1-Apr-2023
=EDATE(A1,-12) // returns 1-Jan-2023

Example - Basic usage

If A1 contains the date February 1, 2018, you can use EDATE like this:

=EDATE(A1,1) // returns March 1, 2018
=EDATE(A1,3) // returns May 1, 2018
=EDATE(A1,-1) // returns January 1, 2018
=EDATE(A1,-2) // returns December 1, 2017

Example - 6 months from today

To use EDATE with today's date, you can combine it with the TODAY function. For example, to create a date exactly 6 months from today, you can use:

=EDATE(TODAY(),6) // 6 months from today

Example - Move by years

To use the EDATE function to move by years, multiply by 12. For example, to move a date forward 2 years, you can use either of these formulas:

=EDATE(A1,24) // forward 2 years
=EDATE(A1,2*12) // forward 2 years

The second form is handy when you already have a value for years in another cell and want to convert it to months inside EDATE.

Example - Sum by month

The EDATE function can be combined with the SUMIFS function to create a formula to sum values by month. This approach is seen in the worksheet below, where EDATE appears in the last argument. The idea is to sum amounts that fall between the first day of the month and the last day of the month. The formula in cell F5 is:

=SUMIFS(amount,date,">="&E5,date,"<"&EDATE(E5,1))

EDATE example - sum amounts in a given month

As the formula is copied down it creates a subtotal for each month listed in column E. You can use this same approach to count by month with COUNTIFS and average by month with AVERAGEIFS. For a detailed explanation and to download the workbook, see this page.

Example - Sequence of months

In Excel 2021 and later, you can use the SEQUENCE function with EDATE to generate a list of sequential months. In the worksheet below, the start date is in cell B5. The formula in D5 creates a list of the next 12 months, including the start date:

EDATE example - sequence of 12 months

If the date in cell B5 is changed, a new list of dates will be generated. For a more detailed explanation, see Sequence of months.

Example - EDATE with time

The EDATE function will strip times from dates that include time (sometimes called a "datetime"). This happens because EDATE only works with whole numbers. To preserve the time in a date, you can use a formula like this:

=EDATE(A1,n)+MOD(A1,1)

Here, the MOD function is used to extract the time from the date in A1, which is then added back to the result from EDATE.

End-of-month dates

EDATE is clever about "end of month" dates when the day is 31. Starting with January 31, 2019, notice that EDATE will keep the last day of the month:

=EDATE("31-Jan-2019",1) // returns 28-Feb-2019
=EDATE("31-Jan-2019",2) // returns 31-Mar-2019
=EDATE("31-Jan-2019",3) // returns 30-Apr-2019
=EDATE("31-Jan-2019",4) // returns 31-May-2019
=EDATE("31-Jan-2019",5) // returns 30-Jun-2019

EDATE will also respect leap years:

=EDATE("31-Jan-2020",1) // returns 29-Feb-2020

However, EDATE will not maintain an end-of-month day when the day is less than 31. For example:

=EDATE("28-Feb-2019",1) // returns 28-Mar-2019

If an end-of-month date is a requirement, switch to the EOMONTH function.

See below for more examples of formulas that use the EDATE function.

Notes

  • EDATE will return the #VALUE error if the start date is not a valid date.
  • If the start date has a fractional time attached, it will be removed.
  • If the months argument contains a decimal value, it will be removed.
  • To returns an end-of-month date, see the EOMONTH function.
  • EDATE returns a date serial number, which must be formatted as a date.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.