Excel Snippet: Get first day and last day of month using an Excel formula

Microsoft ExcelIt doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

The formula, below, can be used to get the first and last days of a month (typically used for accruals); I had to do some research to find the syntax for Excel only to be surprised that it was similar to that of SQL Server (although both are Microsoft products so I maybe should not have been surprised).

Get last day of month:

=EOMONTH(TODAY())

Get last day of last month (same as above but subtract 1 within the function parameters to step back a month):

=EOMONTH(TODAY() , -1)

Get first day of this month (again using the EOMONTH function, but plus 1 outside of the function to add a day):

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

There is an alternate way of getting the first day of this month which uses a different function, but I find using EOMONTH easier to remember:

=TODAY() - DAY(TODAY()) + 1

The TODAY() function could be replaced with a table reference to calculate the first or last day of a month using other dates other than todays.