Using EOMONTH() to get the First and Last Date of a Given Month in Excel & Google Sheets

This update shows a quick and simple example of how to use the EOMONTH function to get the first, and last, date of a given month.

If you want to capture the last date, and / or the first date of the current month, there’s an easy way to achieve this

We’ll start with the Last day of the Month

Excel provides a function specifically for identifying the last date of a month – EOMONTH (EndOfMONTH)

=EOMONTH([Date],[Month_Offset])

Where [Date] it the date containing the month you want to analyse, and [Month_Offset] is used where you want to analyse a relative month (eg, previous month, following month etc)

A very basic example is the last date of the current month…

=EOMONTH(TODAY(),0)

For the first date of the month, we actually have a couple of options. Because it’s always the 1^st, we can use the DATE() function to build our new date.

The DATE function takes 3 parameters and build the date value;

=DATE([Year],[Month],[Day])

Therefore, we might do something like this…

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)

Here, I’ve used the YEAR() and MONTH() functions to extract the relevant date part from the current date (TODAY() function) and then given a day of 1.

However, another option is to utilise the EOMONTH() function we used earlier.

Instead of having an [Offset] parameter of 0, we can use -1 to give us the last date of the previous month, then simply +1 day to this

=EOMONTH(TODAY(),-1)+1

Both approaches will give the same result, but I like to use the latter, as it keeps it consistent with how I’m calculating the End of Month value

These techniques will work equally in Google Sheets as in Excel.