EDATE Function in Microsoft Excel
Part 1: Introduction
Definition
The EDATE function in Microsoft Excel returns the serial number that represents the date, the indicated number of months before or after a specified date (the start_date).
Purpose
The EDATE function calculates maturity dates or due dates that fall on the same day of the month as the issue date.
Syntax & Arguments
EDATE(start_date, months) The EDATE function syntax has the following arguments:
Start_date: A date that represents the start date. Dates should be entered using the DATE function or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May 2008. Problems can occur if dates are entered as text.
Months: The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.
Return value
The EDATE function returns the serial number representing the date, the indicated number of months before or after a specified date.
Remarks
- Microsoft Excel stores date as sequential serial numbers to be used in calculations. By default, January 1, 1900, is serial number 1, and January 1, 2008, is serial number 39448 because it is 39,448 days after January 1, 1900.
- If start_date is not a valid date, EDATE returns the #VALUE! Error value.
- If months is not an integer, it is truncated.
Part 2: Examples
Example 1: Calculate the date after a certain number of months
Purpose: To calculate the date after a certain number of months.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Start Date | End Date |
| 2 | 2023-07-29 | =EDATE(A2, 3) |
| 3 | 2023-08-15 | =EDATE(A3, 6) |
| 4 | 2023-09-01 | =EDATE(A4, 9) |
Explanation: In this example, we want to calculate the date after a certain number of months from the start date. The formula =EDATE(A2, 3) returns the date that is three months after the start date.
Example 2: Calculate the date before a certain number of months
Purpose: To calculate the date before a certain number of months.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Start Date | End Date |
| 2 | 2023-07-29 | =EDATE(A2, -3) |
| 3 | 2023-08-15 | =EDATE(A3, -6) |
| 4 | 2023-09-01 | =EDATE(A4, -9) |
Explanation: In this example, we want to calculate the date a certain number of months from the start date. The formula =EDATE(A2, -3) returns the date that is three months before the start date.
Example 3: Calculate the maturity date of a 6-month term deposit
Purpose: To calculate the maturity date of a 6-month term deposit.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Start Date | Maturity Date |
| 2 | 2023-07-29 | =EDATE(A2, 6) |
| 3 | 2023-08-15 | =EDATE(A3, 6) |
| 4 | 2023-09-01 | =EDATE(A4, 6) |
Explanation: In this example, we want to calculate the maturity date of a 6-month term deposit. The formula =EDATE(A2, 6) returns the date that is six months after the start date.
Example 4: Calculate the due date of a 90-day invoice
Purpose: To calculate the due date of a 90-day invoice.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | Invoice Date | Due Date |
| 2 | 2023-07-29 | =EDATE(A2, 3) |
| 3 | 2023-08-15 | =EDATE(A3, 3) |
| 4 | 2023-09-01 | =EDATE(A4, 3) |
Explanation: In this example, we want to calculate the due date of a 90-day invoice. The formula =EDATE(A2, 3) returns the date that is three months after the invoice date.
Example 5: Calculate the start date of a project based on the end date and project duration
Purpose: To calculate the start date of a project based on the end date and project duration.
Data tables and formulas:
| A | B | |
|---|---|---|
| 1 | End Date | Start Date |
| 2 | 2023-07-29 | =EDATE(A2, -6) |
| 3 | 2023-08-15 | =EDATE(A3, -9) |
| 4 | 2023-09-01 | =EDATE(A4, -12) |
Explanation: In this example, we want to calculate the start date of a project based on the end date and project duration. The formula =EDATE(A2, -6) returns the date that is six months before the end date.
Example 6: Calculate the date after a certain number of months and add a specific number of days
Purpose: To calculate the date after a certain number of months and add specific days.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Days | End Date |
| 2 | 2023-07-29 | 10 | =EDATE(A2, 3) + B2 |
| 3 | 2023-08-15 | 15 | =EDATE(A3, 6) + B3 |
| 4 | 2023-09-01 | 20 | =EDATE(A4, 9) + B4 |
Explanation: In this example, we want to calculate the date after a certain number of months and add a specific number of days. The formula =EDATE(A2, 3) + B2 returns the date three months after the start date and adds ten days.
Example 7: Calculate the date before a certain number of months and subtract a specific number of days
Purpose: To calculate the date before a certain number of months and subtract a specific number of days.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Days | End Date |
| 2 | 2023-07-29 | 10 | =EDATE(A2, -3) – B2 |
| 3 | 2023-08-15 | 15 | =EDATE(A3, -6) – B3 |
| 4 | 2023-09-01 | 20 | =EDATE(A4, -9) – B4 |
Explanation: In this example, we want to calculate the date before a certain number of months and subtract a specific number of days. The formula =EDATE(A2, -3) - B2 returns the date three months before the start date and removes ten days.
Example 8: Calculate the maturity date of a term deposit and add the interest
Purpose: To calculate the maturity date of a term deposit and add the interest.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | Months | Principal | Maturity Value |
| 2 | 2023-07-29 | 6 | 1000 | =EDATE(A2, B2) & ” – ” & C2 * (1 + 0.05)^B2 |
| 3 | 2023-08-15 | 9 | 2000 | =EDATE(A3, B3) & ” – ” & C3 * (1 + 0.05)^B3 |
| 4 | 2023-09-01 | 12 | 3000 | =EDATE(A4, B4) & ” – ” & C4 * (1 + 0.05)^B4 |
Explanation: In this example, we want to calculate the maturity date of a term deposit and add the interest. The formula =EDATE(A2, B2) & " - " & C2 * (1 + 0.05)^B2 returns the date six months after the start date and calculates the maturity value of the deposit.
Example 9: Calculate the due date of an invoice and the late fee
Purpose: To calculate the due date of an invoice and the late cost.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Invoice Date | Days | Amount | Due Date and Late Fee |
| 2 | 2023-07-29 | 30 | 1000 | =EDATE(A2, B2/30) & ” – ” & IF(TODAY() > EDATE(A2, B2/30), C2 * 0.01, 0) |
| 3 | 2023-08-15 | 60 | 2000 | =EDATE(A3, B3/30) & ” – ” & IF(TODAY() > EDATE(A3, B3/30), C3 * 0.01, 0) |
| 4 | 2023-09-01 | 90 | 3000 | =EDATE(A4, B4/30) & ” – ” & IF(TODAY() > EDATE(A4, B4/30), C4 * 0.01, 0) |
Explanation: In this example, we want to calculate the due date of an invoice and the late fee. The formula =EDATE(A2, B2/30) & " - " & IF(TODAY() > EDATE(A2, B2/30), C2 * 0.01, 0) returns the invoice’s due date and calculates the late cost if the current date is later than the due date.
Example 10: Calculate the start date of a project based on the end date and project duration and the total cost
Purpose: To calculate the start date of a project based on the end date, project duration, and total price.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | End Date | Months | Cost per Month | Start Date and Total Cost |
| 2 | 2023-07-29 | 6 | 1000 | =EDATE(A2, -B2) & ” – ” & C2 * B2 |
| 3 | 2023-08-15 | 9 | 2000 | =EDATE(A3, -B3) & ” – ” & C3 * B3 |
| 4 | 2023-09-01 | 12 | 3000 | =EDATE(A4, -B4) & ” – ” & C4 * B4 |
Explanation: In this example, we want to calculate the start date of a project based on the end date, project duration, and total cost. The formula =EDATE(A2, -B2) & " - " & C2 * B2 returns the date six months before the end date and calculates the project’s total cost.
Example 11: Calculate the date after a certain number of months and check if it’s a weekend
Purpose: To calculate the date after a certain number of months and check if it’s a weekend.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Months | Is Weekend |
| 2 | 2023-07-29 | 3 | =IF(WEEKDAY(EDATE(A2, B2), 2) > 5, “Yes”, “No”) |
| 3 | 2023-08-15 | 6 | =IF(WEEKDAY(EDATE(A3, B3), 2) > 5, “Yes”, “No”) |
| 4 | 2023-09-01 | 9 | =IF(WEEKDAY(EDATE(A4, B4), 2) > 5, “Yes”, “No”) |
Explanation: In this example, we want to calculate the date after a certain number of months and check if it’s a weekend. The formula =IF(WEEKDAY(EDATE(A2, B2), 2) > 5, "Yes", "No") returns the date three months after the start date and checks if it’s a weekend.
Example 12: Calculate the date before a certain number of months and check if it’s a weekday
Purpose: To calculate the date before a certain number of months and check if it’s a weekday.
Data tables and formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Start Date | Months | Is Weekday |
| 2 | 2023-07-29 | -3 | =IF(WEEKDAY(EDATE(A2, B2), 2) <= 5, “Yes”, “No”) |
| 3 | 2023-08-15 | -6 | =IF(WEEKDAY(EDATE(A3, B3), 2) <= 5, “Yes”, “No”) |
| 4 | 2023-09-01 | -9 | =IF(WEEKDAY(EDATE(A4, B4), 2) <= 5, “Yes”, “No”) |
Explanation: In this example, we want to calculate the date before a certain number of months and check if it’s a weekday. The formula =IF(WEEKDAY(EDATE(A2, B2), 2) <= 5, "Yes", "No") returns the date three months before the start date and checks if it’s a weekday.
Example 13: Calculate the maturity date of a term deposit and check if it’s a weekend
Purpose: To calculate the maturity date of a term deposit and check if it’s a weekend.
Data tables and formulas:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Start Date | Months | Principal | Maturity Date and Is Weekend |
| 2 | 2023-07-29 | 6 | 1000 | =EDATE(A2, B2) & ” – ” & IF(WEEKDAY(EDATE(A2, B2), 2) > 5, “Yes”, “No”) |
| 3 | 2023-08-15 | 9 | 2000 | =EDATE(A3, B3) & ” – ” & IF(WEEKDAY(EDATE(A3, B3), 2) > 5, “Yes”, “No”) |
| 4 | 2023-09-01 | 12 | 3000 | =EDATE(A4, B4) & ” – ” & IF(WEEKDAY(EDATE(A4, B4), 2) > 5, “Yes”, “No”) |
Explanation: In this example, we want to calculate the maturity date of a term deposit and check if it’s a weekend. The formula =EDATE(A2, B2) & " - " & IF(WEEKDAY(EDATE(A2, B2), 2) > 5, "Yes", "No") returns the date six months after the start date and checks if it’s a weekend.
Part 3: Tips and Tricks
Remember that the EDATE function returns the date’s serial number, not the date itself. You need to format the cell as a date to display the result as a date.
The EDATE function can use date functions like YEAR, MONTH, and DAY to create more complex date calculations.
If you want to calculate the date after a certain number of years, you can multiply the number of years by 12 to convert it to months and use it as the second argument in the EDATE function.
Be careful when entering dates as text. Using the DATE function or the results of other formulas or functions is recommended to avoid errors.
If the month’s argument is not an integer, it will be truncated. For example, if you enter 1.5 as the month’s argument, the EDATE function will treat it as 1.