The EDATE function is one of the most widely used functions for date calculation. It has multiple uses if it is used with other functions of Excel. In this article, we will show the use of the EDATE formula for days with 3 ideal examples.
EDATE Function Overview
- Function Objective:
The EDATE function adds months to a date and returns the date after addition.
- Syntax:
=EDATE(start_date,months)
- Arguments Explanation:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| start_date | Required | It denotes the starting date for the calculation. |
| months | Required | It indicates the number of months to add with the starting date. |
Â
- Return Parameter:
The function returns the date after adding months to the starting date.
- Version:
This function is available for Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, and Excel 365.
It’s time to use the EDATE function. We will show the uses in the following section with 3 different examples. In all cases, the EDATE function will bring out a date after adding specific months to a starting date.
1. Returning Date After Specific Months Using EDATE Formula
In this example, we will show the basic use of the EDATE function. In our dataset, we included the Date, Months to Add, and Result Date columns.
Let’s walk through the procedures for this example.
- Firstly, write the following formula in cell D5 which will add the month value from cell C5 with the date from cell B5.
=EDATE(B5,C5)
- Then, use the Fill Handle to copy the formula in the following cells.
- Finally, we will see month values from the Months to Add column are added to the dates from the Date column, and the result is displayed in the Result Date column.
2. Inserting TODAY Function in EDATE Formula to Get a Date After Certain Days
Now, we will demonstrate the use of the TODAY function with the EDATE function. The TODAY function gives the current date and with that, the EDATE function will add specific months. We are describing the stepwise procedures below for this example.
- Primarily, write the following formula in cell D5.
=EDATE(TODAY(),C5)
- Then, press Enter.
- Further, use the AutoFill feature of Excel to copy the formula in the following cells.
- Finally, we will see different month values added to our current date.
3. Applying the COUNTIFS Function with the EDATE Function to Count Tasks in Specific Periods of Days
In this example, we will use the COUNTIFS function with the EDATE function. For that, we have included some Issue, Starting Date, End Date, and Count columns. In the Count column, we will count the number of Starting Date between the End Date and the next month of the End Date. Follow the stepwise procedures given below.
- Firstly, apply the following formula in cell E5.
=COUNTIFS(($C$5:$C$9),">="&D5,($C$5:$C$9),"<"&EDATE(D5,1))
- Then, hit Enter.
In this formula, the COUNTIFS function counts the number of dates from the range $C$5:$C$9 that satisfies the condition “>=”&D5 and “<“&EDATE(D5,1). The EDATE function finds the date after 1 month from the date of cell D5.
- Afterward, use the Fill Handle to copy the formula in the cells below.
- Finally, we will see the number of Starting Date between End Date and the next month after End Date in Count column.
Read More: [Fixed!] EDATE Function Not Working in Excel
How to Add Days to a Date in Excel
The addition of days to a date is quite simple in Excel. Excel stores the date values as plain numbers starting from 1 January 1900. We will simply add the plain number of days to dates to get the next dates. Follow the stepwise procedures given below.
- Firstly, just write the following formula in cell D5.
=B5+C5
- Then, press Enter.
- Afterward, use the AutoFill feature to copy the formula in the following cells.
- Finally, we will see the number of days from the Days to Add column added to the dates from the Date column and the results are displayed in the Result Date column.
Things to Remember
- The #NUM! error will occur if the date from the calculation is an invalid one.
- Don’t forget to format the cells for dates from the Number Format option in the Excel ribbon.
- If we insert fractional months as the arguments of the EDATE It will discard the fractional value and add the integer part only to the starting date.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Hopefully, you will be able to use the EDATE function in various ways following this article. If you have any queries or suggestions, let us know in the comment section.
<< Go Back to Excel EDATE Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
It would be nice to se your tutorial for counting working days until a future date
Hi RICHARD O’CONNOR,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. You can apply the following formula to calculate working days until a future date. And then, drag the fill handle down to copy the formula in other cells.
=NETWORKDAYS.INTL(C5,D5,1,$G$5:$G$9)Here, Cell C5 is start_date, D5 is end_date, 1 refers to Saturday and Sunday as the weekend, and range $G$5:$G$9 refers to the holidays.
Note: If you skip weekend and holidays arguments in the formula then it will take Saturday and Sunday as weekends and no holidays.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy