EDATE

Description

Use this function to return a date that is the specified number of months before or after the specified date, while keeping the same day of the month. This is useful for calculating maturity dates or due dates that fall on the same day of the month as the date of issue.

Syntax

EDATE(start_date, months)

Inputs

This function has the following arguments:

Name	Required	Valid input
`start_date`	Yes	The date that the result is to be calculated from. This can be a cell reference, an integer date, or a date in the format DD/MM/YYYY or YYYY/MM/DD. A formula which results in one of these is also acceptable.
`months`	Yes	The number of months before or after `start_date`. A positive value months yields a future date; a negative value yields a past date. This can be a cell reference or an integer. A formula which results in either of these is also acceptable.

Examples

Sample data

	A	B
1	February 1, 2018	56
2	April 23, 2019	39
3	July 4, 2000	15
4	7/19/2003	-92
5	12/23/2023	22

Example formulas

Use case Formula Explanation and Result

Add a number of months to a date specified in a cell. =EDATE(A1,56) This formula adds 56 months to the date in cell A1. This formula returns 10/1/2022

Subtract a number of months from a specified date. =EDATE(7/19/2003,-92) This formula adds -92 months to the supplied date of 7/19/2003. This formula returns 11/19/1995.

Generate a sequence of due dates.

=EDATE(A1, SEQUENCE(12))

This formula works in the following manner:

Cell A1 contains the starting date stored as a date.
SEQUENCE(12): Generates a series of numbers from 1 to 12.
EDATE(A1, ...) calculates the dates that are 1, 2, 3...12 months from the starting date, effectively creating a sequence of 12 monthly due dates.

For this data set the formula returns:

3/1/2018

4/1/2018

5/1/2018

6/1/2018

7/1/2018

8/1/2018

9/1/2018

10/1/2018

11/1/2018

12/1/2018

1/1/2019

2/1/2019

Create a list of semi-annual review dates.

=EDATE(A1, SEQUENCE(10, 1, 6, 6))

This formula works in the following manner:

Cell A1 contains the starting date stored as a date.
SEQUENCE(10, 1, 6, 6): Generates a sequence of 10 numbers, starting at 6, with a step of 6, representing the months for the semi-annual reviews (6, 12, 18, etc.).
EDATE(A1, ...) calculates the dates that are 6, 12, 18... months from the starting date, creating a list of semi-annual review dates.

For this data set the formula returns:

8/1/2018

2/1/2019

8/1/2019

2/1/2020

8/1/2020

2/1/2021

8/1/2021

2/1/2022

8/1/2022

2/1/2023

Notes

The Workiva platform stores dates as numbers so they can be used in calculations. By default, January 1, 1900 = 1.
EDATE returns an integer date, which must be formatted as a date.
If the supplied or referenced start_date is not a valid integer date or in the format "DD/MM/YYYY" or "YYYY/MM/DD", EDATE returns the #VALUE! error.
If start_date has a fractional time attached (that is, is not an integer), it is removed.
If months isn't an integer, it's truncated.
If months contains a decimal value, it will be removed.
Wildcards don't work with this function.

Tips

To calculate an end-of-month date, use the EOMONTH function.
The EDATE function will strip times from dates that include time (sometimes called a "datetime").
To preserve the time in a date, you can use a formula like this: =EDATE(start_date,months)+MOD(start_date,1)
The MOD function extracts the time from the date, and then adds it back in.

Related functions

DATE
DATEVALUE
DAY
DAYS
DAYS360
EDATE
EOMONTH
MONTH
NETWORKDAYS
NETWORKDAYS.INTL
WEEKDAY
WORKDAY
WORKDAY.INTL
YEAR
YEARFRAC

Support

Community

Workiva Support Center