Edate & Eomonth Functions: Add Or Subtract Dates In Months In Excel

Excel EDATE and EOMONTH formulas – What excel formulas do you use to calculate due dates? What date will be after the next 21 months? or what date was 19 months ago from the current date?

Maybe some of you will answer by taking the month value with the MONTH function from the current date then adding or subtracting that date and then combining it back into a date format with the DATE function in excel.

This kind of pattern is commonly used to calculate the due date with Microsoft Excel.

Did you know that there is actually an easier way to find out the date before or after a certain number of months. Namely by using the function EDATE or EOMONTH that has been provided by Microsoft Excel.

The EDATE and EOMONTH functions are both used to return a date after or before a number of months. The difference is that Edate will return the same date if it is valid and Eomonth will always return the end date of the resulting month value.

Excel Functions/Formulas	Information
EDATE	Returns the date after or before the number of months. Usually used as an excel formula to calculate the due date
EOMONTH	Returns the end date of the month after or before the number of months.
EDATE vs EOMONTH Rumus formula

To make it clearer, let’s discuss these two excel functions one by one.

Excel EDATE function

The EDATE function is used to get the serial number of a date in Microsoft Excel by calculating the number of months before or after the date we specify or in other words to find out what date is a few months to come or ago.

The use of the EDATE function, for example, is used for the excel formula for the due date that we specify in months.

 

How to Use the Edit Function in Excel

In the excel formula, how to write the Edate function follows the following excel function rules or syntax:

Code:

EDATE(StartDate; Month)

Information:

• StartDate

is the date on which the calculation is based. The Initial Date argument must be a valid excel date format numeric value that can be generated by the DATE function or refer to another cell containing the excel date format.

• Month

Filled with a number indicating the number of months before or after the StartDate . If the value is positive Edate will return the future date and if it is negative Edate will return the date that has passed.

In its use, the two arguments of the Excel Edate function must be filled in and cannot be left blank.

Edate Formula Example

Next, please consider an example of implementing the Edate function in the following excel formula:

In the first example the Edate formula calculates 5 months after February 14, 2017 the result is the number 42930 or if we change the format to date it will show July 14, 2017. While the second formula calculates 3 months after February 14, 2017 the result is November 14, 2016.

For the 3rd example the Edate formula calculates the 4th day of the month after May 31, 2017 with the formula:

Code:

=EDATE(B4;C4)

The Edate formula above should return a value of September 31, 2017. However, as we all know, for September there is no 31st, because the maximum date for September is 30, so Edate produces September 30, 2017.

The above also applies to the 4th formula, where 3 months before May 31, 2017 should be February 31, 2017, but for February 2017 the maximum date is 28, so this Edate formula also produces the value of February 28, 2017 and not February 31 2017.

Excel EOMONTH function

The EOMONTH function is used to get the end date of the month by calculating the number of months before or after the date we specify or in other words to find out the last date of the month after the next few months or before a few months ago.

For example, use the EOMONTH function to calculate the due date which always falls at the end of the month.

The difference between the Edate and Eomonth functions is in the resulting day or date value. Where the Edate function will return the same date value, while the Eomonth function will return the end date of the resulting month.

How to Use the Eomonth Function in Excel

The syntax or way of writing the Edate function in excel formulas is similar to writing the Edate formula.

Code:

EOMONTH(StartDate; Month)

Information:

• StartDate

This is the date on which the calculation will be based. This date argument must be an excel valid date numeric value that can be returned by the DATE function or refer to another cell containing the excel date format.

• Month

Filled with a number indicating the number of months before or after the StartDate . If the value is positive Eomonth will return the end date of the next month and if it is negative Eomonth will return the end date of the month that has passed.

Example of the Eomonth Excel Formula

The following is an example of using the EOMONTH function in a Microsoft Excel formula.

The first formula in the example above calculates 5 months after February 14, 2017 and then returns the end date for that month. 5 months after 14 February 2017 is 14 July 2017, the last date of July 2017 is 31 July 2017.

For the second formula, counting backwards is to find the end of the month 3 months ago from February 14, 2017 and find November 30, 2016.

For example numbers 3 and 4 are more or less the same as the previous 2 examples.

We can also use this Eomonth formula or function to get the end of the current month. For example, as in the example number 5 above:

Code:

=EOMONTH(B6;C6)

By setting the month parameter or argument to 0 then Eomonth will return the end date of the month being searched for.

Well now I’m sure you already know how to calculate the due date with the Edate and Eomonth functions in the Microsoft Excel formula. For other benefits, please develop further according to your individual needs.

Before going, don’t forget to share this excel tutorial and also like and follow the official Excel Class social media accounts for the latest updates to our articles.

Greetings Excel Class.