EDATE Function

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 that derive 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, 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 start date is also the 1st. 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))

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:

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 you need an end-of-month date, 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 return an end-of-month date, see the EOMONTH function.
• EDATE returns a date serial number, which must be formatted as a date.