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
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)
- 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 thus 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-
=EDATE(B5,C5)
- 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)
- 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 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)
- 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.
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)
- Thereafter, click Enter and drag down the “fill handle”.
- In conclusion, the column is filled with expected dates calculated with the current date.
Read More: Excel Current Time Formula (7 Suitable Examples)
Similar Readings
- How to Use TIMEVALUE Function in Excel (4 Examples)
- Use YEAR Function in Excel (5 Examples)
- How to Use TIME Function in Excel (8 Suitable Examples)
- Remove Time from Date in Excel (6 Approaches)
- How to Auto-Update Current Time in Excel (With Formula and VBA)
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-
=EDATE(B7,12*$C$4)
- 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)
- 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 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))
- Now, press Enter and drag down the “fill handle”.
- Without any hesitation you will get all the duplicate dates in a new column.
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)
- 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!
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.
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
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