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