Excel EOMONTH Function

EOMONTH returns the last day of the month, a set number of months before or after a date you specify. It always returns a date, never a range or a text string, and that date is always the final calendar day of whatever month it lands on, whether that's 28, 30, or 31.

Available in: Excel 365, Excel 2021, Excel 2019, Excel 2016, and Excel 2013. Also available in Excel 2010 and 2007. Not available in Excel 2003 or earlier without the Analysis ToolPak add-in.

Syntax

=EOMONTH(start_date, months)

Parameter	Required	Description
`start_date`	Yes	The date you're calculating from. Can be a cell reference, a date typed in quotes, or the result of another formula like `TODAY()`.
`months`	Yes	How many months to shift from `start_date`. Use 0 to stay in the same month, a positive number to move forward, a negative number to move backward.

Basic Example

You're building a subscription billing tracker and need the last day of the billing month for each customer, based on their signup date.

=EOMONTH(B2, 0)

// B2 = the subscription start date
// 0  = stay within the same calendar month

If B2 contains March 15, 2026, this returns March 31, 2026. Change the 15th to any date in March and the formula still returns the 31st, because EOMONTH ignores the day entirely and jumps straight to the end of the month.

How EOMONTH Works

It returns a serial number, not a formatted date

Excel stores every date as a sequential number counting from January 1, 1900. EOMONTH returns one of these numbers, so if your result cell shows something like 46203 instead of a date, format the cell as Short Date or Long Date. The value is correct; the display just hasn't caught up.

The months argument controls direction

Zero keeps you in the same month as start_date. A positive number, like 3, jumps forward three months. A negative number, like -1, jumps back one month. There's no upper or lower limit on how far EOMONTH can travel, as long as the resulting date falls within Excel's supported date range.

It ignores the day component of start_date

EOMONTH only cares what month and year start_date falls in. Whether start_date is the 1st or the 28th, the result is identical. This makes it reliable for month-end calculations even when your source dates are scattered across the month.

It handles leap years automatically

=EOMONTH("2/10/2024", 0) returns February 29, 2024, because 2024 is a leap year. Change the year to 2026 and the same formula returns February 28. You never need to check for leap years manually.

Common Use Cases

Find the last day of the current month

Useful for month-end reports that need to run regardless of when the sheet is opened.

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

Calculate a subscription's next renewal date

Add one month to a signup date, then find that month's last day, for billing cycles that always close on the last calendar day.

=EOMONTH(C2, 1)

// C2 = original signup date
// 1  = advance one month, then return its last day

Get the first day of a month

EOMONTH doesn't return first-of-month dates directly, but one extra step gets you there: find the last day of the previous month, then add 1.

=EOMONTH(D2, -1) + 1

// D2      = any date in the target month
// -1      = step back to the prior month
// + 1     = move one day past its last day, landing on the 1st

Build fiscal quarter-end dates

If your fiscal quarters end in March, June, September, and December, multiples of 3 in the months argument step you through each quarter.

=EOMONTH(E2, 3)   // next quarter-end from E2
=EOMONTH(E2, 6)   // two quarters ahead

Handling Errors

EOMONTH throws #NUM! when the calculation lands outside Excel's valid date range, or when start_date isn't recognized as a date at all.

Common causes of #NUM!:

start_date is text Excel can't parse as a date, like a date typed with periods instead of slashes in some regional settings
The months argument pushes the result before January 1, 1900
start_date is blank or references an empty cell

A generic IFERROR message can't tell you which of these three actually happened. Check the source first instead of catching blindly:

=IF(ISNUMBER(A2), EOMONTH(A2, 1), "A2 isn't a recognized date")

// ISNUMBER(A2) confirms A2 is stored as a real date, not text
// if A2 fails that check, the formula returns a specific message
// instead of letting EOMONTH throw #NUM!

If the date itself checks out and #NUM! still appears, the months argument is likely pushing the result outside Excel's date range:

=IFERROR(EOMONTH(A2, F2), "Result is outside Excel's date range")

✓

If EOMONTH returns #NUM! on a date that looks fine, check whether it's actually stored as text. Left-aligned dates in a cell are a giveaway; real dates align right by default.

Notes & Gotchas

What does the EOMONTH function do in Excel?

EOMONTH returns the last day of a month, calculated a set number of months before or after a starting date. It's built specifically for month-end dates and doesn't work with weeks or years directly. Use it whenever a formula needs to know "the end of this month" or "the end of some other month relative to this one."

How do you use EOMONTH to find the last day of the month?

Pass the date you're working from as start_date and set months to 0. =EOMONTH(A2, 0) returns the last day of whatever month A2 falls in, regardless of which day of the month A2 actually is. This is the most common EOMONTH pattern by far.

What is the difference between EOMONTH and EDATE in Excel?

EOMONTH always returns the last day of a month. EDATE returns the same day-of-month, shifted by a number of months, whatever that day happens to be. =EDATE("1/15/2026", 1) returns February 15, 2026; =EOMONTH("1/15/2026", 1) returns February 28, 2026. Use EDATE for recurring same-day events like a monthly payment due on the 15th, and EOMONTH when you specifically need month-end dates.

How do you get the first day of the month using EOMONTH?

EOMONTH has no built-in "first day" mode, but combining it with subtraction gets there in one step: =EOMONTH(A2, -1) + 1. This finds the last day of the previous month, then adds one day to land on the first day of the current month. It's a two-part trick worth memorizing, since Excel has no dedicated "start of month" function.

⚠

EOMONTH doesn't care whether start_date looks like a date to you. If it's stored as text, even text that displays as "3/15/2026," the formula fails with #NUM! instead of quietly working. Wrap suspicious dates with DATEVALUE() first if you're pulling data from an import or a text file.

Related Functions

Function	Use this when...
`EDATE`	You need the same day of the month, shifted forward or back, instead of a month-end date.
`DATE`	You want to build a specific date from separate year, month, and day values.