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)