How to use the EDATE function in Excel

Excel is a powerful tool for financial modeling and forecasting. One of the most valuable functions for these purposes is the EDATE function.

To add or subtract months from a date in Excel, use the EDATE function. It's simple, efficient, and easy to use. The function returns the serial number that represents the date which is the indicated number of months before or after a specified date (the start_date).

Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. With this function, you can calculate repayment schedules, projections for retirement savings, and other important financial data. It's a reliable and widely-used feature of Excel, so you can trust it always to provide accurate results.

In this article, we'll guide you through the steps of using the EDATE function in Excel so that you can take your financial modeling to the next level.

Syntax and Arguments

The syntax of the EDATE function is simple. The function takes two arguments: a start date and many months. 

You can enter the start date in excel format or as a reference to a cell containing a date.

Enter the number of months as a  positive or negative integer, depending on whether you want to add or subtract months from the start date.

If you are working on a project with many deadlines and want to keep track of when each deadline is due,  use the EDATE function to calculate the due date based on the number of months from the project start date.

To do this, follow these steps:

1. Create a new worksheet in Excel and enter the project start date in cell A1. For example, let's say the project start date is January 1, 2023.
2. In cell A2, enter the first deadline date as a reference to another cell or as a date in Excel format. For example, let's say the first deadline is due 3 months after the project start date, so you can enter "=EDATE(A1, 3)" in cell A2.
3. Copy the formula in cell A2 and paste it into the cells below, to calculate the due date for each next deadline. For example, if the second deadline is due 6 months after the project start date, you can enter "=EDATE(A1, 6)" in cell A3.
4. Format the cells to display the dates in a readable format. You can do this by:
   1. Select the cells
   2. Right-click, and select "Format Cells."
   3. Choose the desired date format from the list.
5. Finally, you can use conditional formatting to highlight cells that contain a date that is past due or coming due soon. To do this:

1. Select the cells
2. Click on "Conditional Formatting" in the ribbon
3. Choose the desired formatting rule.

Should look like this:

With these steps, you can keep track of project deadlines and ensure each complete task is on time.

Examples of EDATE Function Use

The EDATE function is versatile, and you can use it in many financial modeling and forecasting scenarios. For example, you can use the function to calculate the total cost of a loan based on the interest rate, repayment period, and loan amount. 

Use EDATE to forecast retirement savings by adding months until retirement to the current date and calculating the expected rate of return using Excel's financial functions.

Example explanation:

1. In this example, cell A1 contains the date 5/7/2022. The EDATE formula in cell A2 returns a date 3 months from today's date, which is the date the worksheet is opened or refreshed. 
2. The formula in cell A2 is =EDATE(TODAY(), 3). The formula in cell B2 returns the date 3 months from the date in cell A1, which is 6/7/2022.
3. The formulas in cells A3 to A5 are similar, but you can increase the number of months in the EDATE formula by 3 each time. The result in cell B3 is 9/7/2022, which is 6 months from today's date. 
4. The result in cell B4 is 12/7/2022, which is 9 months from today's date. The result in cell B5 is 3/7/2023, which is 12 months from today's date.
5. To format the dates, select the cells with the EDATE formulas, right-click, and choose "Format Cells." In the Format Cells dialog box, choose the desired date format (e.g., "3/14/2012" or "Mar-14-2012").

Should look like this:

Examples of EDATE Function Use

Another example of how to use the EDATE function is calculating the due dates of invoices. Let's say you have a set of invoices due in 30, 60, and 90 days from today's date. 

Use the EDATE function to calculate the due date of each invoice by adding the number of months to the current date. This makes it easy to keep track of payment deadlines and avoid late fees.

Also, you can use the EDATE function to calculate a bond's maturity date. This is particularly useful for investors who want to estimate the value of a bond at a future date. 

You can estimate the bond's future value by adding the number of months to the bond's issue date and then using Excel's financial functions to calculate the expected yield.

Suppose you have the following bills that are due monthly:

• Rent: $1000 due on the 1st of each month
• Internet: $50 due on the 15th of each month
• Electricity: $100 due on the 20th of each month

To calculate the due date of each bill for the next 3 months, you can use the EDATE function along with the DATE function to specify the day of the month. Here's an example Excel worksheet:

Example explanation

1. In this example, cell B2 contains the rent amount of $1,000. The formula in cell C2 uses the DATE function to calculate the due date for the next month's rent, which is the 1st of the month. 
2. The formula is =DATE(YEAR(TODAY()),MONTH(TODAY())+1,1), which returns the date of the 1st day of the following month.
3. The formulas in cells C3 and C4 use the DATE function to calculate the due dates for the next month's internet and electricity bills. The formula in cell C3 is =DATE(YEAR(TODAY()),MONTH(TODAY())+1,15) and the formula in cell C4 is =DATE(YEAR(TODAY()),MONTH(TODAY())+1,20).
4. The formulas in cells B5 to C13 use the EDATE function to calculate the due dates for the following 2 months. The EDATE function adds the number of months specified to the start date. 

For example, the formula in cell C5 is =EDATE(B2,1), which adds 1 month to the start date in cell B2.

Using the EDATE function and the DATE function, you can calculate the due dates for recurring bills for any number of months in advance.

Tips and Tricks for Using EDATE

To get the most out of the EDATE function, it's important to use it in an effective way. Here are some tips and tricks to help you use the function like a pro:

• Use relative cell references: When using the EDATE function, it's important to use relative cell references instead of absolute references. This makes copying and pasting formulas across many cells easier, without updating the cell references each time.
• Apply conditional formatting: You can use conditional formatting to highlight cells that contain a date that is past due or coming due soon. This makes it easy to keep track of payment deadlines and avoid late fees.
• Use the function in combination with other Excel functions: You can combine the EDATE function with other Excel functions, such as the PMT function for calculating loan payments or the FV function for calculating the future value of an investment.

Using these tips and tricks, you can get the most out of the EDATE function and improve your Excel financial modeling and forecasting skills.

Final Thoughts

To recap, EDATE allows you to add or subtract a specified number of months from a given date, making it ideal for calculating loan repayment schedules, retirement savings projections, and more.

To use the function, input the starting date and the number of months to add or subtract and let Excel do the rest.

If you found this article helpful, be sure to share it on your social media channels so others can also benefit. And don't forget to keep exploring the many powerful features of Excel to unlock your full financial potential.

One more thing

We’re glad you’re read this article upto here :) Thank you for reading.

If you have a second, please share this article on your socials; someone else may benefit too. 

Subscribe to our newsletter and be the first to read our future articles, reviews, and blog post right in your email inbox. We also offer deals, promotions, and updates on our products and share them via email. You won’t miss one. 

Keep Learning 

» 13 Tips To Master Excel Without Breaking a Sweat
» Learn About Creating VBA Named Ranges in Excel
» How Do You Unlock Copy and Paste in Excel: A Quick Guide