The Excel EOMONTH function, explained interactively.
EOMONTH returns the last day of a month, offset by N months from a start date. The workhorse for month-end reporting, fiscal boundaries, and billing-cycle math. Leap-year and month-length aware — no 31 vs 30 vs 28/29 logic required on your end.
How to use EOMONTH
- Type
=EOMONTH(and supply a start_date — a cell with a date, aDATE()call, orTODAY(). - Add a comma and months — how many months to offset.
0returns the current month’s end; positive walks forward; negative walks back. - Close the parenthesis. EOMONTH returns a date serial — format the cell as a date to see it rendered readably (
Ctrl+Shift+3in Excel). - Day-of-month in
start_dateis ignored — Jan 3 and Jan 31 both return Jan 31 (or the target month’s actual last day).
Try the months presets — flip to 12 to see EOMONTH handle leap Feb correctly, or -1 to walk back one month from each reference date.
DATE() call, or TODAY() for today-relative math.0 = current month end; positive walks forward; negative walks back. Day-of-month of start_date is ignored — EOMONTH always returns the month’s last day.| A | B | |
|---|---|---|
| 1 | start_date | EOMONTH (months=0) |
| 2 | 2026-01-15Mid-January | 2026-01-31day 31 |
| 3 | 2024-02-10Leap Feb | 2024-02-29day 29 |
| 4 | 2026-07-03Early July | 2026-07-31day 31 |
| 5 | 2026-10-31Last-day Oct | 2026-10-31day 31 |
| 6 | 2026-12-20Mid-Dec | 2026-12-31day 31 |
=EOMONTH(A2, 0). EOMONTH returns a date serial; format the cell as a date to see it rendered. Notice how Feb 28/29 is picked correctly (leap year aware) and the day-of-month of start_date is never echoed through.EOMONTH syntax and arguments
Two required arguments. See Microsoft’s official EOMONTH reference for the canonical specification.
DATE() call, TODAY(), or any formula returning a valid date. Text-date strings are coerced if they match a recognised locale format; if not, EOMONTH returns #VALUE!. Day-of-month is ignored — EOMONTH only reads the year and month.0 returns end of start_date’s month; positive walks forward; negative walks backward. Non-integer values are truncated toward zero. Large offsets are safe — EOMONTH handles year rollover.EOMONTH examples
Four patterns that cover most real-world EOMONTH use.
Example 1: EOMONTH for the current month-end
The canonical usage — today-relative month-end.
Returns the last day of whatever month today falls in. Recalculates every sheet open. Standard for “due at end of month” logic, monthly close dates, and report deadlines.
Example 2: EOMONTH for the first day of a month
EOMONTH only returns last days, so get the first day by going back one month and adding 1.
Returns the first day of A2’s month. The idiom works in every Excel version. Modern Excel also has DATE(YEAR(A2), MONTH(A2), 1) as a cleaner alternative.
Example 3: EOMONTH for quarter-end
Billing cycles, tax quarters, and fiscal reports often close on the last day of the third month.
Returns the end of the calendar quarter that containsA2. The MOD math computes how many months forward to shift to land on a quarter boundary (Mar/Jun/Sep/Dec). Adjust for fiscal years that start off-calendar.
Example 4: Day count from today to month-end
How many days left in this month? Simple subtraction once you have EOMONTH.
Returns an integer count. Useful for pro-rating monthly subscriptions, payroll, or SaaS trials. Pair with IF to warn when fewer than N days remain.
Common EOMONTH errors and fixes
Four failure modes, each with what to check and how to recover.
EOMONTH returns #VALUE!
Cause: start_date is text that Excel can’t parse, or months is non-numeric.
Wrap start_date with DATEVALUE() or build it explicitly with DATE(). For imported data, check the locale — DMY/MDY mismatches cause silent errors.
EOMONTH returns a number instead of a date
Cause: the cell isn’t formatted as a date. EOMONTH always returns a serial.
Apply a date format: Ctrl+Shift+3 in Excel, or Format → Number → Date in Sheets. The stored value is unchanged; rendering switches.
EOMONTH giving unexpected year
Cause: large monthsoffset crosses year boundaries, or the start_date is already near year end.
Verify with =YEAR(EOMONTH(A2, months)). The math is correct — EOMONTH handles year rollover — but the result may surprise if you miscounted months.
EOMONTH on an empty cell returns 1900-01-31
Cause: start_date is blank. Blank cells coerce to 0, which is Excel’s date epoch (1900-01-00 / 1899-12-31 depending on engine).
Guard with IF(A2="", "", EOMONTH(A2, 0)), or wrap with IFERROR. For production sheets, use data validation to require a date in the source column.
EOMONTH vs EDATE, DATE & TODAY
Four date-builders. Pick based on whether you need a month-end, a same-day-offset, an explicit construction, or the current date.
| Function | Inputs | Returns | Use when |
|---|---|---|---|
| EOMONTH | start_date, months | Last day of month after N months | Month-end reports, billing, fiscal closes |
| EDATE | start_date, months | Same day-of-month, shifted N months | “Same day next month” — contracts, anniversaries. Snaps Feb 31 → Feb 28/29. |
| DATE | year, month, day | Explicit date from integers | Known Y/M/D. Rollover handles math. |
| TODAY | (none) | Today’s date | Dashboards and age/deadline calcs. |
Rule of thumb: EOMONTH for month-end, EDATE for month-anniversary, DATE for known-Y/M/D, TODAY for “now”. EOMONTH also doubles as the canonical pattern for month-start math — add 1 to the previous month’s end: EOMONTH(A2, -1) + 1.
EOMONTH frequently asked questions
6.01How do I get the last day of the current month?▸
=EOMONTH(TODAY(), 0) returns today’s month-end. The 0 means “this month”; positive values walk forward (1= next month’s end, 12 = same month next year); negative values walk backward (-1= last month’s end).
6.02What’s the difference between EOMONTH and EDATE?▸
EDATE shifts a date by N months but keeps the same day-of-month (snapping down when needed, e.g. Jan 31 + 1 month = Feb 28). EOMONTH shifts by N months and always returns the last day of the result month. Use EDATE for “same day next month” and EOMONTH for “end of Nth month”.
6.03Does EOMONTH handle leap years correctly?▸
Yes. EOMONTH returns Feb 29 in leap years and Feb 28 otherwise, with no manual logic required. =EOMONTH(DATE(2024, 1, 15), 1) returns 2024-02-29 (leap year); =EOMONTH(DATE(2025, 1, 15), 1) returns 2025-02-28.
6.04Why does EOMONTH return a number like 45000?▸
Excel stores dates as serial numbers counting days since 1 January 1900 (or 1899-12-30 in Google Sheets). EOMONTH returns the serial; if the cell isn’t formatted as a date, you see the raw integer. Apply a date format (Ctrl+Shift+3 in Excel, Format → Number → Date in Sheets) and the display becomes a readable date.
6.05How do I get the first day of a month with EOMONTH?▸
EOMONTH always returns a last day, so for the first day of a target month, add 1 to the previous month’s end: =EOMONTH(A2, -1) + 1 gives the first of A2’s month. =EOMONTH(TODAY(), -1) + 1 gives the first of the current month.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.