Excel EOMONTH Function

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.

Formula

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.

How to Use the EOMONTH Function in Excel? (with Examples)

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

=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 excelTEXT Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more, 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.

Things to Remember

EOMONTH in Excel Video

This article has been a guide to EOMONTH in Excel. We discuss using EOMONTH in Excel and its formula, practical examples, and a downloadable template. You may also look at these useful functions in Excel: –

  • MONTH Function in ExcelYEAR FunctionNOW In Excel | ExamplesWORKDAY in Excel