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.

Thank you for saving me so much time! - Jenny
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course