EOMONTH in Excel

The EOMONTH is a worksheet date function in Excel that calculates the end of the month for the given date by adding a specified number of months to the arguments. This function takes two arguments: one as the date and another as an integer. The output is in date format. The method to use this function is as follows =EOMONTH( start_date, months).

For example, suppose we have data as of 02/04/2020 in column B and the second row. Therefore, the formula will be:

=EOMONTH(B2, 8)

=31/12/2020

Here, to add the last day of December (4+8), the Excel EOMONTH function added 8 and returned the result as 31/12/2020.

It has two arguments, out of which both are required. Where,

• start_date = This represents the starting date. We must enter the date 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 is a serial number that can further be converted into a user-friendly date format using the DATE function.

Example #1 – 1 month forward

As shown in the above EOMONTH formula,

=EOMONTH (B2,1)

The Excel EOMONTH function is applied to cell B2, a date value of 21st August 2018. The second parameter value is 1, indicating one-month forward, September.

Cell C2 represents the resulting cell wherein the value is 43373, the serial number of the resultant date, i.e., the last day of the month, September 2018. The serial number is further converted into a readable date format using the 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 30th September 2018.

Example #2 – 6 Months Backward

As shown in the below EOMONTH formula,

=EOMONTH (B4, -6)

The EOMONTH function in Excel is applied on cell B4, with a date value of 21st Aug 2018. The second parameter value is -6, which indicates February 6-months backward. Cell C4 represents the result cell wherein the value is 43159, the serial number of the resultant date, the last day of February of 2018.

The serial number is further converted into a readable date format using the 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, 28th February 2018.

Example #3 – The Same Month

As shown in the below EOMONTH formula,

=EOMONTH (B6, 0)

The Excel EOMONTH function is applied to cell B6, a date value of 21st August 2018. The second parameter value is 0, which indicates August's the same month. Cell C6 represents the result cell wherein the value is 43343, the resultant date's serial number, the last day of August 2018.

The serial number is further converted into a readable date format using the 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 31st Aug. 2018.

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