How to use the Excel EOMONTH function

Summary

The Excel EOMONTH function returns the last day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month. Use a positive value for months to get a date in the future, and a negative value to get a date in the past.

Purpose

Get last day of month n months in future or past

Return value

Last day of month date

Syntax

=EOMONTH (start_date, months)

Arguments

start_date - A date that represents the start date in a valid Excel serial number format.
months - The number of months before or after start_date.

Usage notes

The EOMONTH function returns the last day of the month, n months in the past or future. Use a positive value for months to get a date in the future, and a negative value to get a date in the past. You can use EDATE to calculate expiration dates, due dates, and other dates that need to land on the last day of a month.

EOMONTH returns a serial number corresponding to a date. To display the result as date, apply a number format of your choice.

Basic usage

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 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

Last day of current month

To get the last day of the current month, combine the TODAY function with EOMONTH like this:

=EOMONTH(TODAY(),0) // last day of current month

The TODAY function returns the current date to the EOMONTH function. EOMONTH, with zero (0) for months, uses the current date to calculate the last day of the current month.

First day of current month

Although EOMONTH returns the last day of the month, you can use EOMONTH to get the first day month of the current month like this:

=EOMONTH(TODAY(),-1)+1 // first day of current month

See links below for more examples of how to use the EOMONTH function in formulas.

Notes

For months, use a positive number for future dates and a negative number for dates in the past.
EOMONTH will return the #VALUE error if the start date is not a valid date.
If the start date has a fractional time attached, it will be removed.
If the months argument contains a decimal value, it will be removed.
To move any date n months into the future or past, see the EDATE function.
EOMONTH returns a date serial number, which must be formatted as a date.

Related functions

Excel EDATE Function

The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the...