It 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 this month:
=EOMONTH(TODAY(), 0)
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.
Click to show/hide the Excel Snippets Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.