|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

EDATE

EDATE

  • Last updated on

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 Description Valid input
start_date Yes The date that the result is to be calculated from.  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. 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

Sample 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("19/07/2003",-92) This formula adds -92 months to the supplied date of 7/19/2003. This formula returns 11/19/1995.
Note that the date must be in double quotation marks.
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

DescriptionUse this function to return a date that is the specified number of months before or after the specified da...