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 EDATE function in excel.


Download Practice Workbook

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


EDATE Function in Excel: Syntax and Argument

EDATE Function in Excel: Syntax and Argument

Summary:

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

Syntax:

=EDATE(start_date,months)

Arguments:

ARGUMENT REQUIRED or OPTIONAL VALUE
start_date Required A starting date is needed to calculate the upcoming date
months Required How many months to go forward or backward is derived by a numeric value.

5 Simple Examples to Use EDATE Function in Excel

In the following, I have shared 5 simple examples to use 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-
=EDATE(B5,C5)

Use EDATE Function to Calculate Date, Month, and Year

  • 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.

Use EDATE Function to Calculate Date, Month, and Year


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 thus we put minus sign() before all the months. Let’s calculate the past dates now.

Use EDATE Function to Calculate Date, Month, and Year

Steps:

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

Use EDATE Function to Calculate Date, Month, and Year

  • 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 EDATE function, you can do it within minutes.

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

Steps:

  • In the same way, put the formula down in the selected cell (D5)-
=EDATE(B5,C5*12)

Use EDATE Function to Calculate Date, Month, and Year

  • 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?

Use EDATE Function to Calculate Date, Month, and Year


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 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-
=EDATE(B5,C5)

Use EDATE Function to Calculate Date, Month, and Year

  • 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.

Use EDATE Function to Calculate Date, Month, and Year

Read More: How to Use DAYS Function in Excel (7 Examples)


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 is providing 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-
=EDATE(TODAY(),B7)

Utilize EDATE and TODAY Functions to Calculate from Today's Date

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

Utilize EDATE and TODAY Functions to Calculate from Today's Date

Read More: Excel Current Time Formula (7 Suitable Examples)


Similar Readings


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.

Combine EDATE and YEARFRAC Functions to Determine Retirement Date

Step 1:

  • Select a cell (C7) and put the formula down-
=EDATE(B7,12*$C$4)

Combine EDATE and YEARFRAC Functions to Determine Retirement Date

  • 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-
=YEARFRAC(TODAY(),C7)

Combine EDATE and YEARFRAC Functions to Determine Retirement Date

  • 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.

Combine EDATE and YEARFRAC Functions to Determine Retirement Date

Read More: How to Use DATE Function in Excel (8 Examples)


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-
=COUNTIFS(($C$5:$C$16),">=" & E5,($C$5:$C$16),"<" & EDATE(E5,1))

Apply COUNTIFS and EDATE Functions to Count Duplicate Date

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

Apply COUNTIFS and EDATE Functions to Count Duplicate Date

Read More: How to Use DAY Function in Excel (3 Ideal Examples)


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-
=EDATE(B5,C5)+MOD(D5,1)

Utilize EDATE and MOD Functions to Combine Date and Time

  • 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 a blink of an eye. Enjoy!

Utilize EDATE and MOD Functions to Combine Date and Time

Read More: How to Use Excel MONTH Function (6 Examples)


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 “#VALUE” error in some cells. This happens when the entered date is not a valid date.

Conclusion

In this article, I have tried to cover all the examples to use 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.


Related Articles

Taryn N

Taryn N

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

2 Comments
  1. that works fantastically. can you help me with a problem please?

    this is the formula that i am currently using: =datedif(b1,b2,”Y”)& ” years, “&datedif(b1,b2,”YM”)& ” months, “&datedif(b1,b2,”MD”)& ” days. ” and that shows me the years, months and days. how do you work out the years, months, weeks and days please?

    many thanks

    Mark

  2. Hi Mark,
    Use the following formula instead of the formula you’re using.
    =DATEDIF($B$1,$B$2,”Y”)& ” years, “&DATEDIF($B$1,$B$2,”YM”)& ” months, “&ROUNDDOWN(DATEDIF($B$1,$B$2,”MD”)/7,0)&” Weeks, and “&DATEDIF($B$1,$B$2,”MD”)-ROUNDDOWN(DATEDIF($B$1,$B$2,”MD”)/7,0)*7&” Days.”
    Regards
    Kawser

Leave a reply

ExcelDemy
Logo