# How to Use EDATE Function in Excel- 4 Examples

Adding or removing a certain number of months from a given date is done using the **EDATE **function in Excel.

In this article, we will learn -How to use the **EDATE **function in Excel with examples and explanations

## Syntax of EDATE Function

`=EDATE(date, month_number)`

## Argument of EDATE Function

**date:** The date to which you want to add or subtract months. The date supplied to the argument needs to be in date format.

**month_number: **The total number of months you want to adjust. If the value is positive, months will be added to the date; if it is negative, months will be subtracted.

## Return of EDATE Function

A serial number representing the date that is the specified number of months before or after the date is returned by the **EDATE** function in Excel.

## Example #1: Simple application of the EDATE function

- Adding or subtracting a certain number of months from a date

To add two months to a date in cell B3, use the following formula:

`=EDATE(B3,2)`

- To subtract 2 months from a date in cell B4, the formula would be as follows:

`=EDATE(B4,-2)`

- Using a specified number of months to determine a past or future date:

A date that is 12 months from the current date can be calculated using the formula below:

`=EDATE(TODAY(),12)`

The **EDATE** function will return the respective serial number of the given date according to Excel’s 1900 system.

### Retrieve the Date Format from the Return of EDATE Function:

You can retrieve the date format from the serial number returned by the EDATE function by applying the formula below:

`=TEXT(C3,"mm/dd/yyyy")`

Rather than applying the formula you can use Excel’s built-in formatting by following the steps

**Step 01: **Select the date range **C3:C5**

**Step 02: **From the **Home** tab go to the** Number **section and click on **Number format**

**Step 03:** From the drop-down list, click on **Short Date**

The result is,

## Example #2: EDATE Function for Years

Suppose you are given a list of dates as below and you want to add 2 years to each.

As the **EDATE** function works with months only, you have to convert years to months by multiplying years by 12.

`=EDATE(B3,2*12)`

Then format the serial number to date format as the steps given earlier

## Examples #3: Determine the Final Day of the Particular Months

You can use the **EOMONTH** function along with the **EDATE** function to determine the date of the last day of a particular month.

Before jumping to the example, we recommend knowing about the **EOMONTH** function in Excel.

Suppose you want to know the last day of the next month from the list given in the previous example.

To meet your purpose apply the following formula

`=EOMONTH(EDATE(B3,1),0)`

Explanation:

- With the
**EDATE**function, a predefined number of months can be added or subtracted from the given date. In this case, we are going to take the given date and add one month to it to get a date that is one month later than the given date. - After then, the
**EDATE**function’s output is subjected to the**EOMONTH**function. The**EOMONTH**returns at the end day of the month, which is one month after the date that was provided.

## Examples #4: Application of the EDATE Function in Conditional Formatting

Suppose you have a range of dates in cells **B3:B6**, and you want to count the number of dates that fall within the next 6 months from today’s date.

The **COUNTIF** function can be used to count the number of dates that fall between today’s date and the end date after using the **EDATE** function to determine the 6-month period’s end date.

`=COUNTIF(B3:B6,">="&TODAY())-COUNTIF(B3:B6,">"&EDATE(TODAY(),6))`

Explanation:

- The first
**COUNTIF**function counts the number of dates greater than or equal to today’s date in the range**B3:B6**. - The second
**COUNTIF**function counts the number of dates in the same range that are greater than the end date of the 6-month period, which is calculated using the**EDATE**function. - The number of dates that fall within the 6-month timeframe is determined by the difference between the two counts.

### Conditional Formatting by EDATE Function:

You want to highlight the texts that fall within the range of the given date and the day 6 months from today.

**Step 01:** Select the date range **B3:B6**

**Step 02:** From the **Home** tab, go to the style section and click on “**Conditional Formatting**”

**Step 03: **From the drop-down list, click on “**New Rules.**“

**Step 04: **A new window will pop up named “**New Formatting Rule.””** Then click on **Use a formula to determine which cells to format.**

**Step 05:** Input the formula given below in the field named **Edit the rule description **

`=COUNTIF(B3,">="&TODAY())-COUNTIF(B3,">"&EDATE(TODAY(),6))`

**Step 06: **Click on the **Format **Button

**Step 07: **A new window will appear named **Format Cells**. Select the color you prefer after clicking the** Color** option.

**Step 08: **Click on the **OK** button

**Step 09:** Again, click on the** OK** button in the** Conditional Formatting** window.

The result is:

## Things to Keep in Mind About the EDATE Function

**#VALUE! error:**If the**EDATE**function is used in conjunction with another function, such as**SUM**or**IF**, and the input arguments are incompatible with the #NUM! error,**#VALUE!**error may result.-
**#NUM! error:**If the number of months input argument is not an integer or produces an invalid date, this error will occur. **#REF! error:**If the cell reference used in the**EDATE**function is deleted or altered, an error will result.

## DATE vs EDATE function in Excel

- While Excel’s
**DATE**and**EDATE**functions both deal with dates, their objectives and input parameters differ. The**EDATE**function modifies an existing date by adding or removing a predetermined number of months, whereas the**DATE**function creates a new date. - While the
**EDATE**function only requires two input arguments, the**DATE**function requires three (year, month, day)

## Frequently Asked Questions

### How do I apply the EDATE formula for days, weeks, months, and years?

With Excel’s** EDATE** function, you can add or subtract a certain number of months from a given date. Days, weeks, and years cannot be directly added to or subtracted by using the** EDATE** function in Excel.

You may think if we convert the days or weeks to months by dividing them by 30 and 4, we may achieve the result. But this will be a big mistake.

See the example below:

If you apply any fraction of a month to the **EDATE** function’s syntax, the **EDATE** function will return the value after rounding up the fraction. That’s why same dates are returned in this example.

Rather, you can apply the following simple solution to add weeks and days:

To add days

`=B3+2 //returns the date after adding with B3`

To add weeks

`=B3+7*2 //returns the date after adding 2 weeks or 14 days`

To add the year the** EDATE** function can be applied (Please see the Example #2)