EOMONTH Function – Last Day of Month (Excel, VBA, G Sheets)

This tutorial demonstrates how to use the EOMONTH Function in Excel to find the last day of the month, n months away.

How to use the EOMONTH Function

Current Month

The EOMONTH Function takes a date and returns the last day of a month:

=EOMONTH(B3,0)

Previous Month

You can also add (or subtract) n months to the start date to get the last day of another month. This example will return the last day of the previous month:

=EOMONTH(B3,-1)

Number of Days in Month

By using the DAY Function, you can calculate the number of days in the month:

=DAY(EOMONTH(B3,0))

The DAY Function returns the day number of a date. By using EOMONTH, we can get the last day of the month and the DAY function then returns the corresponding day number.

First Day of Month

By adding 1 to the result of the EOMONTH Function, you can get the first day of a month:

=EOMONTH(B3,-1)+1

Note: in Excel, dates are stored as serial numbers where each number represents a day, so adding 1 to a date, will add 1 day.

EOMONTH in Google Sheets

The EOMONTH Function works exactly the same in Google Sheets as in Excel:

EOMONTH Examples in VBA

You can also use the EOMONTH function in VBA. Type:

application.worksheetfunction.eomonth(start_date,months)

Running the following VBA statements

Range("B2") = Application.WorksheetFunction.EoMonth(Range("A2"), 0)
Range("B5") = Application.WorksheetFunction.EoMonth(Range("A5"), -1)
Range("B8") = Application.WorksheetFunction.EoMonth(Range("A8"), -1) + 1

Will produce the following results

For the function arguments (start_date, etc.), you can either enter them directly into the function, or define variables to use instead.