Excel EOMONTH Function: Complete Guide
EOMONTH — end of month — returns the last day of the month N months away from a start date. It's the workhorse for month-end reporting, invoice due dates, and any calculation that anchors to the last calendar day of a period.
Syntax
| Argument | Type | Description |
|---|---|---|
start_date Required | Date | The anchor date. Any day of the month — EOMONTH returns the last day regardless of your input day. |
months Required | Number | How many months to move forward (positive) or backward (negative). 0 = same month. |
Basic examples
End of current month
=EOMONTH(TODAY(), 0)
End of next month
=EOMONTH(TODAY(), 1)
End of prior month
=EOMONTH(TODAY(), -1)
End of month 6 months out
=EOMONTH(TODAY(), 6)
Common uses
1. First day of the month
Getting to the first of a month
=EOMONTH(A1, -1) + 1
2. First day of next month
Month-boundary math
=EOMONTH(A1, 0) + 1
3. Days remaining in month
Days until month-end
=EOMONTH(TODAY(), 0) - TODAY()
4. Month-end grouping for a PivotTable
Bucket transactions by month-end
=EOMONTH(A1, 0)
5. Quarter-end date
End of current calendar quarter
=EOMONTH(TODAY(), MOD(3 - MOD(MONTH(TODAY()), 3), 3))
6. Fiscal quarter-end (April-start FY)
End of fiscal quarter for AU/UK FY
=EOMONTH(A1, MOD(3 - MOD(MONTH(A1) - 4, 3), 3))
7. Payment due date (Net 30 to month-end)
End of following month
=EOMONTH(A1, 1)
EOMONTH vs. EDATE — the key difference
| Input | EDATE(start, 3) | EOMONTH(start, 3) |
|---|---|---|
| Feb 15, 2026 | May 15, 2026 | May 31, 2026 |
| Feb 28, 2026 | May 28, 2026 | May 31, 2026 |
| Jan 31, 2026 | Apr 30, 2026 (clipped) | Apr 30, 2026 |
EDATE preserves the day-of-month (clipping when necessary — Jan 31 + 1 month = Feb 28). EOMONTH always returns the last day.
The month-end anchor pattern
For time-series analysis, aligning everything to month-ends is standard. Add a "Period End" column with =EOMONTH([Date], 0). Now:
- PivotTables group cleanly by month
- Multi-period comparisons align by month-end
- Aggregations don't fragment across month boundaries
Common pitfalls
If EOMONTH returns 46326 instead of "September 30, 2026", the cell is formatted as Number or General. Right-click → Format Cells → Date.
EOMONTH ignores the day part of start_date. EOMONTH("2026-09-06", 0) and EOMONTH("2026-09-30", 0) both return Sep 30. This is intentional but can surprise you.
To count transactions in a specific month regardless of day: =COUNTIFS(dateColumn, ">="&EOMONTH(A1,-1)+1, dateColumn, "<="&EOMONTH(A1,0)). Anchoring both bounds to EOMONTH prevents day-boundary bugs.
EOMONTH in Google Sheets
Google Sheets has an identical EOMONTH function. Same syntax, same behavior. In Google Sheets you can also use QUERY with date functions for more complex month-end reporting.
Related functions
Month-end math without the head-scratching
Getting the first day of a month, the last day, or the end of a fiscal quarter each requires a slightly different EOMONTH pattern. Excel Wizard handles it — describe what you need ("last day of the quarter") and it writes the right EOMONTH combo.
Install Excel Wizard →Frequently asked questions
What does EOMONTH do in Excel?
EOMONTH returns the last day of the month N months from a start date. Syntax: =EOMONTH(start_date, months).
How do I get the last day of the current month?
Use =EOMONTH(TODAY(), 0). Returns the last day of the current month.
How do I get the first day of a month?
Use =EOMONTH(A1, -1) + 1. EOMONTH with -1 gives the last day of the previous month; adding 1 gives the first day of the current month.
What's the difference between EOMONTH and EDATE?
EDATE returns the same day of the month N months later. EOMONTH returns the last day of that month. Use EDATE for anniversaries; EOMONTH for month-end reporting.