The EOMONTH function is one of those little gems in Excel that can save you a lot of trouble. It’s a simple function that does just one thing: given a date, it returns the last day of a month.
This may seem a little silly — how hard can it be to figure out the last day of a month? — but it’s actually a bit tricky, since each month has a different numbers of days, and February changes in leap years.
Enter the EOMONTH function
EOMONTH takes two arguments: a start date, and months. Months represents the number of months to move in the future or past. So, for example, with May 12, 2017 in cell B5:
=EOMONTH(B5,0) returns May 31, 2017
=EOMONTH(B5,4) returns Sep 30, 2017
=EOMONTH(B5,-3) returns Feb 28, 2017
You can easily use EOMONTH to move through years as well:
=EOMONTH(B5,12) returns May 31, 2018
=EOMONTH(B5,36) returns May 31, 2020
=EOMONTH(B5,-24) returns May 31, 2015
You can also use EOMONTH to easily get the first day of the next month:
=EOMONTH(B5,0)+1 returns Jun 1, 2017
Notes on using EOMONTH
- Make sure you give EOMONTH a proper date to start. You can use the DATE function if you need to assemble a date from scratch.
- Make sure you apply date formatting to the result of EOMONTH, otherwise you may see just a big number.
- If you want to move to the same date in past or future months, use the EDATE function.
Example formulas built on EOMONTH
Building on EOMONTH’s simple utility, you can build all kinds of useful formulas. Here are a few examples to give you some inspiration:
- Sum by month
- Days in month
- Get last working day in month
- Calculate retirement date
- Get first day of previous month
More formulas
Want to learn more formulas? Need help with formulas?
We have more than 500 formulas examples, and high-quality video training if you like learning with a structured program.
