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:

=EOMonth(Start_Date, Months)

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!

  1. A label that always shows the current month:
    • =EOMonth(Today(),-1)+1
    • Then, apply custom formatting “MMM” to shorten the display to month name only.
  2. 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.
  3. The first day of the current year (useful for dynamically updating):
    • =EOMonth(Today(), -Month(Today()))+1
  4. 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!