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.
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 |
EDATE function Syntax
EDATE(start_date, months)
EDATE function Arguments
| 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.
EDATE function example
The image above demonstrates the EDATE function in cells D3 and D4, the start dates are specified in cells B3 and B4. The number of months are in cells C3 and C4.
Formula in cell D3:
The first date in cell B3 is "1/15/2017", the number of months in cell C3 and the result in cell D3 is "9/15/2017" which is exactly 8 months later.
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.
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.
Dynamic array formula in cell D3:
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}.
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
EDATE 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.
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
The following article has a formula that contains the EDATE function.
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
Functions in 'Date and Time' category
The EDATE function function is one of many 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