The Excel EOMONTH (End of Month) Function

Date and time: Returns the last day of the month a specified number of months after a given date.

The EOMONTH Function returns the date serial number for the last day of the month specified.  That can be an indicated number of months before or after a specified date (the start_date). Use it to calculate maturity dates or due dates that fall on the last day of the month. But the function is not sophisticated enough to avoid weekends (for example)..Syntax

Syntax:

EOMONTH(start_date, months)

Arguments:

Start_date (Required). The start date.  You can enter dates using the DATE function, or they can occur as results of other formulas or functions. For example, use DATE(2022,8,23) for 23 August 2022.  Problems can occur with dates entered as text.

Months (Required). The number of months before or after start_date. A positive value for months yields a future date; a negative value yields a past date.

Remarks:

Microsoft Excel stores dates as sequential serial numbers so you can use them in calculations. 1 January 1900 is serial number 1.  Thus, 1 January 2000 is serial number 36526 because it is 36,525 days after 1 January 1900.

If start_date is not a valid date, EOMONTH returns the #VALUE! error value.

If months is not an integer, Excel ignores the decimal part.

Example

Column C documents the data and formulas in column A.

In cell A2, type 2022-01-13 (or any other date you like) and press Enter.  The date should appear in the default format for your computer.  The column width should adjust to show all the data.

We use yyyy-mm-dd format for entry because it is unambiguous.  In the USA, 01/02/2022 means January 2, 2022, whereas the rest of the world sees it as 1 February.

In cell A3, enter =EOMONTH(A2,0).  The result will be the last day of the month of the date in A2.

In cell A4, enter =EOMONTH(A2,1).  The result will be the last day of the month after the date in A2.

In cell A5, enter =EOMONTH(A2,-2). [note the “minus two”!].  You should see the last day of the month two months before the date in A2.

In cell A6, enter =EOMONTH(“30 Feb 2022”,0) [or use any other invalid date].  The result will be #VALUE!

Here is another way to get the end of the month, for the previous month only:  In cell A7, enter =A2 – DAY(A2)

Bonus – First Day of This Month

How to get the first of the month from any date in the month:
=start_date – DAY(start_date) + 1 or
=EOMONTH(start_date,-1) + 1

Note: I sometimes confuse EOMONTH (end of the month) with EDATE (add months to a date).  EDATE is also helpful in the right context.