How to use the EDATE function
What is the EDATE function?
The EDATE function returns a date determined by a start date and a number representing the months. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue.
Table of Contents
1. Introduction
What are dates in Excel?
Dates are stored numerically but formatted to display in human-readable date/time formats, this enables Excel to do work with dates in calculations.
For example, dates are stored as sequential serial numbers with 1 being January 1, 1900 by default. The integer part (whole number) represents the date the decimal part represents the time.
This allows dates to easily be formatted to display in many date/time formats like mm/dd/yyyy, dd/mm/yyyy and so on and still be part of calculations as long as the date is stored numerically in a cell.
You can try this yourself, type 10000 in a cell, press CTRL + 1 and change the cell's formatting to date, press with left mouse button on OK. The cell now shows 5/18/1927.
What is a maturity date?
The maturity date is the date on which the principal amount of a security becomes due and payable to the holder. It applies to fixed-income securities like bonds, notes, bills where the issuer must repay the principal on the maturity date.
At maturity, the debt is fully repaid. The security might cease to exist after maturity. Maturity dates affect interest rate risk - longer terms have higher risk. The final coupon payment is made on the maturity date.
What is a due date?
The due date is the date on which a debt payment is due to be paid by a borrower to a lender. It applies to loans, mortgages, credit cards, accounts receivables where periodic payments are required.
A due date recurrence can be monthly, quarterly, annually based on the terms. Missing a due date can result in late fees or interest charges.
Related functions
| Excel Function | Description |
|---|---|
| DATEDIF(start_date, end_date, unit) | Returns the time between two dates in specified units like complete years or months |
| EDATE(start_date, months) | Returns the date that is the indicated number of months before or after start_date |
| EOMONTH(start_date, months) | Returns the last day of the month before or after start_date by months |
| MONTH(date) | Returns the month of a date (1-12) |
| DATE(year, month, day) | Returns an Excel date |
2. Syntax
EDATE(start_date, months)
| start_date | Required. A start date. |
| months | Required. The number of months before or after start_date. You are allowed to use negative numbers here. A negative number makes the function return an earlier date than the start date. |
The first argument is an Excel date, make sure it is not invalid. The second argument is an integer, negative values are also possible.
3. Example
The image above demonstrates how to use the EDATE function. The start dates are specified in cells B3:B7. The number of months are in cells C3:C7.
The first example is in cell B3, it contains date 1/15/2024 or January 15th, 2024. The second argument is specified in cell C3 which is 8 meaning eight months.
Formula in cell D3:
The result in cell D3 is "9/15/2024" which is exactly 8 months later of 1/15/2024. 1+8 equals 9. 9 represents September which is 8 months after January.
The second example is in cell B4, it contains date 10/17/2026 or October 17th, 2026. The second argument is specified in cell C4 it contains -11 meaning negative 11 months.
Formula in cell D4:
The result in cell D4 is "11/17/2025" which is exactly 11 months before 10/17/2026. 11 represents November which is 11 months before October.
The third example is in cell B5, it contains date 3/7/2025 or March 7th, 2025. The second argument is in cell C5 which contains -13 meaning negative 13 months.
Formula in cell D5:
The result in cell D5 is 2/7/2024 which is exactly 13 months before 3/7/2025. 2 represents February which is 13 months before March.
The fourth example presented in cell B6 contains date 4/8/2023 or April 8th, 2023. The second argument is in cell C6 which contains 22 meaning 22 months into the future.
Formula in cell D6:
The result in cell D6 is 2/8/2025 which is exactly 22 months after 4/8/2023. 2 represents February which is 22 months after April.
The fifth example shown in cell B7 contains date 7/22/2028 or July 22nd, 2028. The second argument is in cell C7 which contains -25 meaning negative 25 months.
Formula in cell D7:
The result in cell D7 is 6/22/2026 which is exactly 25 months before 7/22/2028. 6 represents June which is 25 months before July.
4. Calculate quarterly dates
This example demonstrates how to calculate four different dates in the same calculation using arrays in Excel. The calculated dates are exactly three months a part from the start date.
Dynamic array formula in cell D3:
{3;6;9;12} is an array of numbers separated by a semicolon. Excel performs four different calculations in the same cell and returns an array to cell D3 and the cells below as far as needed. This is called spilling and is a new feature in Excel 365. This works in earlier Excel versions as well, however, you are required to enter the formula as an array formula for it to work.
5. Calculate bi-monthly dates
This example demonstrates how to calculate six different dates in the same calculation using arrays in Excel. The calculated dates are exactly two months a part from the start date.
Excel 365 dynamic array formula in cell D3:
The formula six dates with an bi-monthly interval starting from the given date in cell B3. Cell B3 contains 1/15/2017. The output array in cell D3 and cells below contains: 3/15/2017, 5/15/2017, 7/15/2017, 9/15/2017, 11/15/2017, and 1/15/2018.
Explaining formula in cell D3
Step 1 - Create a sequence from 2 to 12 with step 2
The SEQUENCE function creates a list of sequential numbers.
Function syntax: SEQUENCE(rows, [columns], [start], [step])
SEQUENCE(6,,2,2)
returns
{2;4;6;8;10;12}
Step 2 - Calculate dates
EDATE(B3,SEQUENCE(6,,2,2))
becomes
EDATE(B3,{2;4;6;8;10;12})
and returns
{42809;42870;42931;42993;43054;43115}.
6. EDATE function tips and tricks
Use a negative number as the month argument to get a date before the start date. The image above shows the start date in cell B3, -2 in cell C3 which represents the month argument and the result in cell D3.
2/27/2030 minus two months is 12/27/2029
7. Function not working
An invalid date demonstrated in cell B5 returns a #VALUE! error displayed in cell D5. Use the method explain here to convert invalid dates to dates that work: DATEVALUE function
The EDATE function truncates a decimal value representing months argument, shown in cell C3 and D3.
What is truncate?
Removing the decimal leaving only the whole number. For example, truncate 7.9 and you get 7.
8. EDATE function alternative
The DATE function creates an Excel date based on three arguments: year, month, and day. It can be used to calculate a date for a given number of months in the future, or in the past, just like the EDATE function.
- year - a number containing four digits equal to or larger than 1900.
- month - a number between 1 and 12 representing the months in a year: January to December.
- a - variable representing the months argument in the EDATE function.
- day - a number representing the day of the date from 1 to 31.
'EDATE' function examples
Table of Contents Excel monthly calendar - VBA Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
Functions in 'Date and Time' category
The EDATE function function is one of 22 functions in the 'Date and Time' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form