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)
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…
For the first date of the month, we actually have a couple of options. Because it’s always the 1st, we can use the DATE() function to build our new date.
The DATE function takes 3 parameters and build the date value;
Therefore, we might do something like this…
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
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.