ID EN
…
#Date and time

EOMONTH

Excel Functions

Get last day of month n months in future or past

Syntax

EXCEL 
=EOMONTH(start_date, months)

Arguments

Parameter Description
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.

Return Value

Last day of month date

Details

The EOMONTH function returns the last day of the month, a given number of months in the past or future. You can use EOMONTH to calculate expiration dates, due dates, and other dates that must land on the last day of a month. EOMONTH returns a serial number corresponding to an Excel date. To display the result as a date, apply a number format of your choice. The EOMONTH function takes two arguments, start_date and months: Note: The EOMONTH function returns the last day of the month. If you want the same day of the month, use the EDATE function. With a given start date, EOMONTH returns a new date by adding the number of months provided and returning the last day of the resulting month. To illustrate how this works, assume we want to create dates for the last day of each quarter, starting fro

Examples

The EOMONTH function explained

With a given start date, EOMONTH returns a new date by adding the number of months provided and returning the last day of the resulting month. To illu

EXCEL 
=EOMONTH("1-Jan-2024",2) // returns 31-Mar-2024
=EOMONTH("1-Jan-2024",5) // returns 30-Jun-2024
=EOMONTH("1-Jan-2024",8) // returns 30-Sep-2024
=EOMONTH("1-Jan-2024",11) // returns 31-Dec-2024
The EOMONTH function explained

The first formula adds 2 months, the second adds 5 months, the third adds 8 months, and the fourth adds 11 months to the date. Although the start date

EXCEL 
=EOMONTH(A1,2) // returns 31-Mar-2024
=EOMONTH(A1,5) // returns 30-Jun-2024
=EOMONTH(A1,8) // returns 30-Sep-2024
=EOMONTH(A1,11) // returns 31-Dec-2024
The EOMONTH function explained

The results are the same. And if the date in A1 is changed, EOMONTH will generate new dates. You can use negative numbers for months to create dates b

EXCEL 
=EOMONTH(A1,-1) // returns 31-Dec-2023
=EOMONTH(A1,-4) // returns 30-Sep-2023
=EOMONTH(A1,-7) // returns 30-Jun-2023
=EOMONTH(A1,-10) // returns 31-Mar-2023
Example - Basic usage

With the date May 12, 2017, in cell B5, the formulas below will return the dates as noted:

EXCEL 
=EOMONTH(B5,0) // returns May 31, 2017
=EOMONTH(B5,4) // returns Sep 30, 2017
=EOMONTH(B5,-3) // returns Feb 28, 2017
Example - Move by years

To use the EOMONTH function to move by years, multiply the months by 12. For example, to move a date forward 2 years, you can use either of these form

EXCEL 
=EOMONTH(A1,24) // forward 2 years
=EOMONTH(A1,2*12) // forward 2 years
Example - Last day of the current month

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

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

See Also

EDATE