Finding the beginning and ending dates of months using the EOMONTH function

Since there is no consistent length for a month (they range from 28 to 31 days), new Excel users often struggling with finding the beginning and ending dates of a given month in Excel using formulas. But it turns out that there's a fairly easy solution to their problems: the EOMONTH function. We can find the beginning or ending dates of any given month, as well as the prior and upcoming months, using this function.

Finding the end of a month using EOMONTH

In its most basic form, the EOMONTH function helps us find the end date of a given month. It's syntax is:

=EOMONTH(start_date, months)

EOMONTH takes a given start_date, travels months months from that date, and then outputs the last day of the month in which it lands. If months is set to 0, then EOMONTH will output the last day of the month in which start_date falls.

That may sound a little confusing at first, but things will quickly become clear once we take a look at some examples:

=EOMONTH("5/6/2019", 0)
Output: 5/31/2019, or serial number 43,616

Above, since months is set to 0, EOMONTH finds the last day of the month in which the date 5/6/2019 falls. The output is 5/31/2019, since May has 31 days.

If we wanted to find the last day of the following month, we would do it like so:

=EOMONTH("5/6/2019", 1)
Output: 6/30/2019, or serial number 43,646

Since we've changed the months argument to 1, EOMONTH now looks for the last day of the month following 5/6/2019, which is 6/30/2019 — because June is the month after May, and it has 30 days.

Note that we can also use a negative months argument to find the last day of the prior month, like so:

=EOMONTH("5/6/2019", -1)
Output: 4/30/3019, or serial number 43,585

Finding the first day of a month

In many cases, you may want to find the first day of a month, rather than the last, in Excel. For example, let's say that we want to find the first day of the month in which the date 5/6/2019 falls. How do we do this?

The answer is simple: we use math! There is no beginning-of-month function built into Excel, but we can build our own proxy by finding the end of the prior month using the formula above and then adding one day. For example:

=EOMONTH("5/6/2019", -1) + 1
Output: 5/1/2019, or serial number 43,586

Above, to find the first day of the month in which 5/6/2019 falls, we run EOMONTH on 5/6/2019 with months set to -1, then add 1 to the result. The day following the last day of the previous month is, of course, the first day of the current month!

We can also use the same trick to find the first day of the month following the current date, like so:

=EOMONTH("5/6/2019", 0) + 1
Output: 6/1/2019, or serial number 43,617

It's that easy — you've learned how to use EOMONTH to find the beginning and end dates of months based on a given day input. If you've got questions or comments on the techniques above, be sure to let us know in the Comments section below.

Comments