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:
You can easily use EOMONTH to move through years as well:
You can also use EOMONTH to easily get the first day of the next month:
Notes on using EOMONTH
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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
