EOMONTH Function

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:

• start_date - a valid Excel date to use as the starting point. The day of the month does not matter. EOMONTH will return the last day of the month regardless.
• months - a whole number that specifies how many months to move. Use a positive number of months to get a date in the future and a negative number for a date in the past.

Note: The EOMONTH function returns the last day of the month. If you want the same day of the month, use the EDATE function.

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 illustrate how this works, assume we want to create dates for the last day of each quarter, starting from January 1, 2024. We can do this with the following formulas:

=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 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 is the 1st of the month, EOMONTH returns the last day of the month in all cases after adding months. Of course, hardcoding a date into a formula doesn't make much sense in Excel. A better option is to use a cell reference. If we enter the date January 1, 2024, in cell A1, the same formulas look like this:

=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 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 before the start date. With the same date in A1 (1-Jan-2024), the formulas below return end-of-quarter dates for the previous year:

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

=EOMONTH(B5,0) // returns May 31, 2017
=EOMONTH(B5,4) // returns Sep 30, 2017
=EOMONTH(B5,-3) // returns Feb 28, 2017

Notice that although the start date is the 12th of May, the result from EOMONTH is always the last day of the month.

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

=EOMONTH(A1,24) // forward 2 years
=EOMONTH(A1,2*12) // forward 2 years

The second form is handy when you already have a value for years in another cell.

Example  - Last day of the 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, and the value for months is given as zero. The result is that EOMONTH will return the last day of the current month. Because the TODAY function will continue to recalculate over time, this formula will always return the correct result.

Example - First day of the current month

Although the EOMONTH function returns the last day of a month, you can easily adjust the formula above to return the first day of the current month like this:

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

The formula works like this:

1. The TODAY function returns the current date to EOMONTH.
2. EOMONTH moves back to the last day of the previous month.
3. Adding 1 returns the first day of the current month.

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

Example - Sum by month

The EOMONTH function will often turn up in formulas that perform month-based calculations. For example, in the worksheet below, the goal is to sum amounts by month and by client. The formula in G5 is:

=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))

In this example, the values in G4:I4 are actually first-of-month dates, formatted to display only an abbreviated month name. The SUMIFS function is configured to use EOMONTH to create "brackets" for each month that are used to sum the amounts in column D by month and by client. For a full explanation of how this formula works, see this page.

Example - Sequence of months

In Excel 2021 and later, you can use the SEQUENCE function with EOMONTH to create a list of end-of-month dates. In the worksheet below, the start date is in cell B5. The formula in D5 looks like this:

=EOMONTH(B5,SEQUENCE(12))

The SEQUENCE function returns an array of 12 numbers like {1;2;3;4;5;6;7;8;9;10;11;12} directly to the EOMONTH function:

=EOMONTH(B5,{1;2;3;4;5;6;7;8;9;10;11;12})

EOMONTH then generates 12 end-of-month dates starting with the month after 15 June 2025. If the date in cell B5 is changed, a new list of dates will be generated. For a more detailed explanation of this idea, see Sequence of months.

Notes

• For months, use a positive number for future dates and a negative number for past dates.
• 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.