EOMONTH in Excel (Formula, Example) | How to Use EOMONTH Function?

Excel Functions Tutorials

EOMONTH Function (Table of Contents)

EOMONTH Excel
EOMONTH Formula in Excel
How to Use EOMONTH Function in Excel?

EOMONTH in Excel

EOMONTH in Excel is a built-in function in Microsoft Excel. EOMONTH function returns the last day of the month which is ‘x’ months later or in the past. The application of EOMONTH in excel is to calculate the maturity dates or the due dates that is applicable on the last day of the month.

EOMONTH Formula in Excel

The EOMONTH formula in Excel is as follows:

The EOMONTH Formula in excel has two arguments out of which both are required. Where,

start_date = This represents the starting date. The date must be entered by using the DATE function. E.g.: DATE (2018,5,15)
months = The number of months before or after the start_date. If the number is positive, it indicates the future date. If the number is negative, it yields a date in the past.

The return value of the EOMONTH function in excel is a serial number which can further be converted into a user-friendly date format using DATE function.

How to Use EOMONTH Function in Excel?

EOMONTH in Excel is a Worksheet (WS) function. As a WS function, EOMONTH Function in excel can be entered as a part of the formula in a cell of a worksheet. Refer to the examples given below to understand EOMONTH better.

EOMONTH in Excel Worksheet

Let’s look at the EOMONTH in Excel examples given below. Each example covers a different use case implemented using EOMONTH in Excel.

EOMONTH in Excel Example #1 – 1 month forward

As shown in the above EOMONTH formula,

=EOMONTH (B2,1)

EOMONTH function in Excel is applied on the cell B2 which has date value as 21^st Aug’ 2018. The 2^nd parameter value is 1 which indicates 1-month forward i.e. September. Cell C2 represents the resulting cell wherein the value is 43373 which is the serial number of the resultant date i.e. the last day of the month September of the year 2018. The serial number is further converted into a readable date format using TEXT function in excel which takes the value to be converted and the date format as its parameters. Here, the value from cell C2 is converted into the date format as ‘dd/mm/yyyy’ and the resultant date is displayed in cell D2 which is 30^th Sept. 2018.

EOMONTH in Excel Example #2 – 6 months backward

As shown in the below EOMONTH formula,

=EOMONTH (B4, -6)

EOMONTH in excel is applied on the cell B4 which has date value as 21^st Aug’ 2018. The 2^nd parameter value is -6 which indicates 6-month backward i.e. February. Cell C4 represents the result cell wherein the value is 43159 which is the serial number of the resultant date i.e. the last day of the month February of the year 2018.

The serial number is further converted into a readable date format using TEXT function in excel which takes the value to be converted and the date format as its parameters. Here, the value from cell C4 is converted into the date format as ‘dd/mm/yyyy’ and the resultant date is displayed in cell D4 which is 28^th Feb. 2018.

EOMONTH in Excel Example #3 – The same month

As shown in the below EOMONTH formula,

=EOMONTH (B6, 0)

EOMONTH in excel is applied on the cell B6 which has date value as 21^st Aug’ 2018. The 2^nd parameter value is 0 which indicates the same month i.e. August. Cell C6 represents the result cell wherein the value is 43343 which is the serial number of the resultant date i.e. the last day of the month August of year 2018.

The serial number is further converted into a readable date format using TEXT function in excel which takes the value to be converted and the date format as its parameters. Here, the value from cell C6 is converted into the date format as ‘dd/mm/yyyy’ and the resultant date is displayed in cell D4 which is 31^st Aug. 2018.

Things to Remember About the EOMONTH Function in Excel

If the start_date is not a valid date, EOMONTH in excel returns the #NUM! indicating an error in the number.
If the resultant date i.e. after adding or deducting the given number of months (the 2^nd parameter) is invalid then, EOMONTH excel returns #NUM! indicating an error in the number.
If the start_date is written in an un-appropriate format then, EOMONTH function in excel returns #VALUE! indicating an error in the value.
The return value of the EOMONTH function excel is a serial number which can further be converted into a user-friendly date format using DATE Excel function.
By default, Excel treats January 1, 1900, as serial number 1, and January 1, 2008, as 39448 indicating it is 39,448 days after January 1, 1900.

Usage of EOMONTH in Excel VBA

The EOMONTH function is not readily available in Excel VBA.

You can download this EOMONTH Function template here – EOMONTH Function in Excel Template

EOMONTH in Excel