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
Finding the end of a month using EOMONTH
In its most basic form, the
EOMONTH( start_date, months)
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)
5/31/2019, or serial number 43,616
If we wanted to find the last day of the following month, we would do it like so:
EOMONTH( "5/6/2019", 1)
6/30/2019, or serial number 43,646
Since we've changed the
Note that we can also use a negative
EOMONTH( "5/6/2019", -1)
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
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
5/1/2019, or serial number 43,586
Above, to find the first day of the month in which
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
6/1/2019, or serial number 43,617
It's that easy — you've learned how to use
Save an hour of work a day with these 5 advanced Excel tricks
Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.
- How to create beautiful table formatting instantly...
- Why to rethink the way you do VLOOKUPs...
- Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...