How to Use EDATE Function in Excel (5 Simple Examples)

While working in Microsoft Excel we often need to calculate dates. But without proper function, you won’t be able to do it. Using the EDATE function you can calculate the future, past, month-end date, and many more. Today in this article, I am sharing with you how to use the EDATE function in Excel.

EDATE Function in Excel: Syntax and Argument

Summary:

• Provide a Date according to the month number given in the argument.

Syntax:

=EDATE(start_date,months)

Arguments:

How to Use EDATE Function in Excel: 5 Simple Examples

In the following, I have shared 5 simple examples to use the EDATE function in Excel.

1. Use EDATE Function to Calculate Date, Month, and Year

Using the EDATE function you can calculate the past dates, upcoming dates, end of the month, and years too. Below I have explained calculating these dates with proper examples. Follow the instructions properly.

1.1 Calculate Future Date

In this submethod, we will calculate the future date.

Suppose we have a dataset with some Dates. In addition, we have a list of Months too. Now we will calculate the future date from the dataset using the EDATE function.

Steps:

• First, select a cell (D5) and write the following formula down-

• Second, press Enter to get the output.
• To finish, drag the “fill handle” down to fill all the cells.

• Finally, we have all the upcoming dates calculated from the months’ list.

1.2 Calculate Past Date

Imagine you want to calculate dates before a given date. Here we have a data table of some Dates and Months. As we are calculating the past days we put minus sign(–) before all the months. Let’s calculate the past dates now.

Steps:

• Above all, choose a cell (D5) from the worksheet to apply the following formula-

• Simply hit the Enter button and drag down the “fill handle” to get the final output.
• In summary, we have successfully got our past days as we wanted.

1.3 Calculate Year

Suppose we have to calculate years from a given date. With proper utilization of the EDATE function, you can do it within minutes.

Imagine a dataset with some Dates and Years. Thus we will determine the date with years.

Steps:

• In the same way, put the formula down in the selected cell (D5)-

• Gently, press Enter to continue.
• Hence, drag the “fill handle” down to fill with the calculated dates in the column.
• As I said, within minutes we have our precious output in our hands. Simple isn’t it?

1.4 Calculate End of Month

Sometimes your boss might tell you to calculate the end of every month from some given dates so that the salary and working days can be calculated easily. No worries! I have a quick formula to calculate the end of the month from any date.

Suppose we have a list of Dates and Months on a worksheet. Now we will calculate the End of Month according to the table.

Steps:

• Presently, we will start with selecting a cell (D5) from the worksheet to write the formula-

• In the same fashion, hit Enter and pull the “fill handle” down.
• As a result, the final column will display all the end of month’s date.

2. Combine EDATE and TODAY Functions to Calculate from Today’s Date

While working we often need to calculate dates from today’s date. For that, you don’t need to fill today’s date with all the cells. Combining the EDATE and TODAY function calculate any date from today’s date. Here the TODAY function provides the current date in the string. Follow the steps below-

From the following screenshot, you will see we have some Months list which we will calculate with today’s date.

Steps:

• In a likewise manner, choose a cell (C7) to apply the formula-

• Thereafter, click Enter and drag down the “fill handle”.
• In conclusion, the column is filled with expected dates calculated with the current date.

3. Combine EDATE and YEARFRAC Functions to Determine Retirement Date

If you are looking for a solution to calculate the retirement date and you aren’t getting the proper function then you are at the right place. With the help of the EDATE function, we are going to determine the date of retirement and the YEARFRAC function will subtract between the dates to provide the output as the remaining retirement days.

Suppose we have a dataset with Birth Date and Service Year. Using functions we will calculate Retirement Date and Years Left.

Step 1:

• Select a cell (C7) and put the formula down-

• Therefore, click Enter and pull the “fill handle” down.
• In a glimpse, you will get the retirement date in a new column.

Step 2:

• Again we will choose a new cell (D7) and apply the following formula-

• Simply, press Enter and drag the ‘fill handle” down to get the proper output.
• Thus we will get some numeric values in the column. Here, let’s take the first output from cell (D7). The output is “4” which represents 2 years and 4 months remaining. Hope you got what you are searching for.

Read More: How to Use EDATE Formula for Days

4. Merge COUNTIFS and EDATE Functions to Count Duplicate Date

You can also count duplicate dates with the combination of COUNTIFS and EDATE functions.

Imagine we have a dataset with some Works and scheduled Target Time. But the Target Date has duplicates. To find it go through the steps below-

From the below screenshot, you will see we have all the month’s names in cells (E5:E16). Using the COUNTIFS and EDATE functions first we will search for the duplicates from the given dates with the helper column (E) and place them in a new column.

Steps:

• First, select a cell (F5) and put the formula down-

• Now, press Enter and drag down the “fill handle”.
• Without any hesitation, you will get all the duplicate dates in a new column.

5. Combine EDATE and MOD Functions to Combine Date and Time

Imagine a dataset with Date, Month, and Time in multiple columns. If we put them into one cell it will be easy to access, isn’t it? Let’s combine them in a new column calculating the future date with the month column using the EDATE function. To add the time we will utilize the MOD function too.

Steps:

• Above all, select a cell (E5) and write the following formula-

• Just hit the Enter button and pull the “fill handle” down to fill with the expected results.
• Finally, we have the date and time in a new column within the blink of an eye. Enjoy!

Read More: [Fixed!] EDATE Function Not Working in Excel

Things to Remember

• Sometimes you will find numeric values instead of dates. To solve this don’t forget to change the cell format to “Date”. Press Ctrl+1 to open the “Format Cells” window. From the window choose “Date” and click OK to change cell format to date format.
• After applying formulas to call you might find the “#VALUE” error in some cells. This happens when the entered date is not a valid date.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Conclusion

In this article, I have tried to cover all the examples of using the EDATE function in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.

<< Go Back to Excel Functions | Learn Excel