Exceljet

Quick, clean, and to the point

The EOMONTH function - Formula Friday

by Dave Bruns | March 10, 2017

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 function basic usage example

=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 function example moving by years

=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

  1. 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.
  2. Make sure you apply date formatting to the result of EOMONTH, otherwise you may see just a big number.
  3. 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:

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 need to learn quickly with a structured program.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables