Excel EOMONTH Function: Find the Last Day of a Month
Returns the last day of the month, a given number of months before or after a date.
EOMONTH returns the last day of a month, which sounds narrow until you have tried to work it out yourself. Months are 28, 29, 30 or 31 days long and February changes its mind every four years, so any formula you build by hand will be wrong occasionally. EOMONTH is right always.
The second argument is how many months to move first: 0 for this month, 1 for next, -1 for last, -12 for the same month a year ago. That is what makes it a scheduling function rather than a lookup — payment terms, reporting periods, and rolling twelve-month windows all fall out of it.
The best-known trick is that adding 1 to the result gives the first day of the following month, which is otherwise surprisingly awkward to express.
Syntax
=EOMONTH(start_date, months)Arguments
start_dateRequired- The date to start from.
monthsRequired- How many months to move before taking the month end. 0 is the current month; negatives go backwards. It is required, not optional.
The example data
Headers in row 1, data in A2:C5. Columns B and C hold real dates.
| A | B | C | |
|---|---|---|---|
| 1 | Project | Start | Due |
| 2 | Warehouse move | 2024-01-15 | 2024-04-30 |
| 3 | Site survey | 2024-02-01 | 2024-02-14 |
| 4 | Fit-out | 2024-03-11 | 2024-09-01 |
| 5 | Handover | 2024-08-19 | 2024-10-07 |
Worked examples
=EOMONTH(B2, 0)Result: 2024-01-31
The end of the month the start date falls in.
=EOMONTH(B2, 1)Result: 2024-02-29
One month on. 2024 is a leap year, and EOMONTH knows without being told.
=EOMONTH(B2, 0) + 1Result: 2024-02-01
The first of the next month — the standard way of getting it.
=EOMONTH(B2, -1) + 1Result: 2024-01-01
And the first of the *current* month: go back one month end, then step forward a day.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
Result shows as 45322
Why it happens: EOMONTH returns a date serial number, and the cell is formatted as General rather than as a date.
How to fix it: Format the cell as a date. The value is already correct.
#NUM!
Why it happens: The calculated date falls outside Excel's supported range, or start_date is not a valid date.
How to fix it: Check the start date is a real date and the months offset is not wildly large.
#VALUE!
Why it happens: start_date is text Excel does not recognise as a date.
How to fix it: Convert with DATEVALUE, or fix the underlying column — text dates usually arrive from an import.
Missing the months argument
Why it happens: Unlike many optional-looking arguments, months is required.
How to fix it: Pass 0 explicitly for the current month.
Tips worth knowing
- EOMONTH(date, 0) + 1 is first-of-next-month; EOMONTH(date, -1) + 1 is first-of-this-month.
- Payment terms of end-of-month-plus-30 are EOMONTH(invoice, 0) + 30, not invoice + 30.
- Pair it with SUMIFS for a monthly total: criteria of ">="&EOMONTH(d,-1)+1 and "<="&EOMONTH(d,0).
- EDATE is the sibling that keeps the day of the month rather than jumping to the end — use it for anniversaries.
Frequently asked questions
How do I get the first day of the month?
=EOMONTH(A2, -1) + 1. Go back to the previous month end and step forward one day. =DATE(YEAR(A2), MONTH(A2), 1) does the same thing and is arguably clearer.
Why does EOMONTH return a number instead of a date?
Excel stores every date as a serial number and relies on cell formatting to display it. The result is correct; format the cell as a date and it will look like one.
What is the difference between EOMONTH and EDATE?
Both move a date by a number of months. EDATE keeps the same day of the month, so 15 January plus one month is 15 February. EOMONTH always lands on the last day of the target month regardless of where it started.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.