Monthly reports always end on a moving target: January ends on the 31st, February on the 28th (or 29th), April on the 30th. Typing those dates by hand is how month-end reports go wrong. EOMONTH calculates the last day of any month for you — this month, next month, or six months from now.
What EOMONTH does
EOMONTH takes a start date and a number of months, and returns the last day of the month that many months away. Zero means the same month, 1 means next month, -1 means last month. It handles leap years and short months automatically, which is the entire reason to use it instead of adding 30 days.
The syntax
=EOMONTH(start_date, months)
- start_date — any date in the starting month, for example A2.
- months — how many months to move: 0 for the same month, 1 for next month, -1 for last month.
Step-by-step example
Find the last day of this month and the next two months from a start date.
- Click the cell where the answer should go.
- Type
=EOMONTH(A2,0)where A2 holds any date this month — the result is the last day of this month. - Type
=EOMONTH(A2,1)in the next cell for the last day of next month. - For a billing date that is always the month-end, copy the formula down a column of start dates — each row gets its own month-end.
- If the result shows a number like 46022 instead of a date, format the cell as a date (Ctrl+1, then Date). The value is right; only the display is wrong.
Worked example
| Start date | Months | Result |
|---|---|---|
| 2026-01-15 | 0 | 2026-01-31 |
| 2026-01-15 | 1 | 2026-02-28 |
| 2026-02-10 | 1 | 2026-03-31 |
| 2026-12-05 | 2 | 2027-02-28 |
February 2026 ends on the 28th because 2026 is not a leap year, while adding 2 months to December 5th lands on February 28th, 2027. EOMONTH knows the calendar so you do not have to.
Common errors
#VALUE! — the start is not a date. A date stored as text breaks EOMONTH. Convert it first with DATEVALUE, or re-enter it as a real date.
The result is a serial number. 46022 is January 31st, 2026, displayed as a number. Format the cell as a date and it reads correctly.
Off by one month. EOMONTH(A2,1) from January 31st gives February 28th — the last day of next month. If you wanted the same day next month, use EDATE instead, which keeps the day number.
It returns the wrong year. Adding 12 months to a December date crosses into the next year — that is correct behaviour, not a bug. Check the year in the result before assuming an error.
Tip
Pair EOMONTH with SUMIFS to build month totals: sum the amounts where the date is greater than EOMONTH of last month and less than or equal to EOMONTH of this month. That pattern powers most monthly reports.
First day of the month, and days left
EOMONTH also gives you the first day of a month: =EOMONTH(A2,-1)+1 is the first day of A2’s month, because the last day of last month plus one day is the first of this month. And =EOMONTH(TODAY(),0)-TODAY() tells you how many days are left in the current month — handy for countdowns and for prorating a monthly budget. Together, the first-day and last-day formulas bracket any month exactly, which is all a monthly report ever needs: every date from the first to the last, with nothing leaking into the next month.
Practice file
Download the EOMONTH practice file (.xlsx) — it contains start dates and live EOMONTH formulas for month-ends, plus a blank sheet for you to try it yourself.
Google Sheets
Google Sheets supports EOMONTH with the same syntax. Sheets also has EDATE for the same-day-next-month calculation, so the EOMONTH-versus-EDATE choice works identically in both apps.
Example prices and figures are illustrative, not market data.