One if my favorite simple Excel formulas is EOMonth. When working with reports over multiple date ranges or just creating a calendar, it’s a quick tool to make life easy. Unfortunately, it’s name obscures a bit of it’s usefulness. Yes, it technically returns the last day of a month, but this can easily hijacked for all sort’s of wonderful things!
Here’s the basic format:
Give the formula a date and a number and it will return back a date. For example:
- =EOMonth(“1-1-2019”, 0) will return “1-31-2019”
- =EOMonth(“1-12-2019”, 0) will return “1-31-2019”
- =EOMonth(‘1-12-2019″, 2) will return “3-31-2019”
- =EOMonth(“1-12-2019, -1) will return “12-31-2019”
How’s this useful? Glad you asked!
- A label that always shows the current month:
- Then, apply custom formatting “MMM” to shorten the display to month name only.
- Working days in the current month:
- =NetWorkDays(EOMonth(Today(),-1)+1, EOMonth(Today(), 0))
- You could do a similar formula with “Days” instead to calculate the total days rather than the working days.
- The first day of the current year (useful for dynamically updating):
- =EOMonth(Today(), -Month(Today()))+1
- For reports “by month”, I prefer to use formulas rather than hand-typing each month. Use the formula above for January and then for each cell above:
- =EOMonth(*reference cell above*, 0)+1
- Fill the formula down the column and each cell will have the next month.
Dates can be tricky to wrangle in Excel, but EOMonth helps a ton!