Excel EOMONTH Function
last modified October 5, 2026
The EOMONTH function is a date function in Excel that returns the
serial number of the last day of the month that is a given number of months
before or after a start date. It is widely used for financial periods, due
dates, maturity dates, and any calculation that must land on a month boundary.
This tutorial covers the syntax, the month-end logic, negative month values,
leap years, the difference between EOMONTH and EDATE,
and the errors you are likely to meet.
EOMONTH Function Basics
The EOMONTH function calculates the last day of a month based on a
given start date. It can look forward or backward by a number of months. The day
of the start date is ignored: the result is always a month-end.
| Component | Description |
|---|---|
| Function Name | EOMONTH |
| Syntax | =EOMONTH(start_date, months) |
| Arguments | start_date: starting date months: months before or after start_date |
| Return Value | Serial number of the last day of the target month |
This table breaks down the essential components of the EOMONTH
function. Both arguments are required, and the function returns a date serial
number, not a formatted date.
| Argument | Meaning |
|---|---|
| start_date | The starting date. It can be a literal such as
DATE(2025,4,15), a cell reference, or the result of another
function such as TODAY(). Dates typed as text may not be
recognized. |
| months | The number of months to move before taking the month-end. A positive value moves forward, a negative value moves backward, and 0 stays in the current month. Decimal values are truncated toward zero. |
Like other date functions, EOMONTH returns an Excel serial number.
For example, April 30, 2025 is serial 45777. Apply a date number format, for
instance with Ctrl+1, to display it as a calendar date.
How EOMONTH Finds the Month-End
EOMONTH works in two steps. First it shifts the start date by the
requested number of months, then it moves to the last day of that resulting
month. Because the second step overrides the day, the day component of the start
date never affects the result.
| Start date | months | Result |
|---|---|---|
| 2023-01-15 | 0 | 2023-01-31 |
| 2023-01-31 | 1 | 2023-02-28 |
| 2024-01-15 | 1 | 2024-02-29 |
| 2023-03-10 | -1 | 2023-02-28 |
| 2023-01-15 | 12 | 2024-01-31 |
The first row shows that the 15th of January still yields January 31. The second row shows the leap-year rule in a common year: January 31 plus one month is February 28 in 2023. The third row shows February 29 in 2024, a leap year. The fourth row moves backward, and the fifth row shows that 12 months is exactly one year.
Basic EOMONTH Example
This example demonstrates the simplest use of EOMONTH to find the
month-end for a specific date.
=EOMONTH("2025-04-15", 0)
This formula returns the last day of April 2025, which is April 30, 2025, or serial number 45777. The result appears as a number until you apply a date format.
EOMONTH with Positive Months
Adding positive months moves forward in time. This example shows finding the month-end several months ahead.
| A | B |
|---|---|
| 2025-01-15 | |
| =EOMONTH(A1, 3) |
The table shows a start date in cell A1 and an EOMONTH formula in
B2 that looks 3 months ahead.
=EOMONTH(A1, 3)
For January 15, 2025, this returns April 30, 2025 (serial 45777). Positive numbers move forward in the calendar, and the result always falls on the last day of the target month.
EOMONTH with Negative Months
Negative month values look backward in time. This example finds a previous month-end date.
| A | B |
|---|---|
| 2025-06-20 | |
| =EOMONTH(A1, -2) |
The table demonstrates using EOMONTH to look back 2 months from
June 20, 2025. The result will be April 30, 2025. Negative values are useful for
historical comparisons.
=EOMONTH(A1, -2)
For June 20, 2025, this gives April 30, 2025 (serial 45777). Negative numbers move backward in time, one calendar month at a time.
EOMONTH and Leap Years
Because EOMONTH always returns a real month-end, it handles leap
years automatically. The same formula can produce different dates depending on
the year.
| Start date | Formula | Result |
|---|---|---|
| 2023-01-31 | =EOMONTH(A1, 1) | 2023-02-28 |
| 2024-01-31 | =EOMONTH(A1, 1) | 2024-02-29 |
January 31 plus one month is February 28 in 2023 and February 29 in 2024. The function never returns an invalid date such as February 30 or February 31.
Months Is Truncated Toward Zero
If the months argument is not a whole number, Excel discards the
fractional part rather than rounding. The value is truncated toward zero, so
positive and negative decimals both move to the smaller magnitude.
=EOMONTH(DATE(2025,1,15), 2.9) ' same as months = 2, returns March 31, 2025 =EOMONTH(DATE(2025,1,15), -2.9) ' same as months = -2, returns November 30, 2024
If you need true rounding, round the value yourself, for example with
=EOMONTH(A1, ROUND(B1,0)). Otherwise a value such as 2.9 behaves
exactly like 2.
EOMONTH for Fiscal Year End
EOMONTH can calculate fiscal year-end dates. This example shows a
June 30 fiscal year end calculation.
| A | B |
|---|---|
| 2025-03-15 | |
| =EOMONTH(A1, 3) |
The table shows how to calculate the next fiscal year-end (June 30) from a date in March. The formula adds 3 months to reach June 30, 2025.
=EOMONTH(A1, 3)
For March 15, 2025, adding 3 months gives June 30, 2025 (serial 45838). Adjust the month offset to match your own fiscal calendar.
EOMONTH with Dynamic Date
Combine EOMONTH with TODAY() for dynamic month-end
calculations. This example shows finding the current month's end date.
=EOMONTH(TODAY(), 0)
This formula returns the last day of the current month by using
TODAY() as the start date and 0 months. It recalculates whenever the
workbook is opened, so it always shows the current month-end date.
EOMONTH for Period Boundaries
Month-end dates are natural boundaries for reporting periods. Adding one day to the previous month-end gives the first day of the current month, and pairing that with the current month-end creates a complete period.
=EOMONTH(TODAY(), -1) + 1 ' first day of the current month =EOMONTH(DATE(2025,4,1), 0) ' April 30, 2025, the period end =SUMIFS(amount, dates, ">="&start, dates, "<="&EOMONTH(start,0))
The SUMIFS line totals every value in the amount range
whose date in the dates range falls between the first day of a
month and that month's last day. Because the upper bound is computed with
EOMONTH, the formula keeps working when the period length changes.
EOMONTH vs EDATE
EOMONTH and EDATE both shift a date by a number of
months, but they return different days. EDATE keeps the day of the
start date, while EOMONTH always returns the last day of the
resulting month.
| Start date | EDATE(A1, 1) | EOMONTH(A1, 1) |
|---|---|---|
| 2025-01-15 | 2025-02-15 | 2025-02-28 |
| 2025-01-31 | 2025-02-28 | 2025-02-28 |
The first row shows the essential difference: EDATE returns the
15th, while EOMONTH returns the 28th. The second row shows that they
agree when the start date is already a month-end. A DATE-based
formula such as =DATE(YEAR(A1), MONTH(A1)+1, DAY(A1)) behaves like
EDATE for most dates, but it can overflow into the next month when
the day does not exist, whereas both EDATE and EOMONTH
clamp to a valid date.
EOMONTH Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| start_date is text that Excel cannot read as a date | #VALUE! error | Pass a real date, for example DATE(2025,4,15) or a cell
reference |
| Result falls before January 1, 1900 or after December 31, 9999 | #NUM! error | Reduce the size of the months offset |
| months is text rather than a number | #VALUE! error | Convert the value with VALUE or N |
| months contains a decimal | Result is off by one month from the rounded value | The value is truncated toward zero; use ROUND first |
| Cell shows a number such as 45777 | Serial number instead of a date | Apply a date number format with Ctrl+1 |
| You expected the same day of the month | Result is always a month-end | Use EDATE when the day must be preserved |
The EOMONTH function is essential for financial reporting, project
planning, and any date-based calculation that needs a month boundary. It ignores
the day of the start date, moves forward or backward by whole months, handles
leap years automatically, and returns a serial number that must be formatted as
a date. Remember that decimal months are truncated and that an out-of-range
result produces #NUM!. Once you pair EOMONTH with
TODAY, EDATE, and SUMIFS, building
dynamic period reports in Excel becomes straightforward and dependable.
Author
List all Excel Formulas.