Date & time
How to Use EOMONTH in Excel (End of Month and Due Dates)
EOMONTH returns the last day of a month, a set number of months before or after a date you give it. It is the go-to function for month-end reporting, billing cycles, and payment terms like "due at the end of the following month".
Because it always lands on the final day, EOMONTH also handles short months and leap years for you, so you never have to remember whether February has 28 or 29 days. This guide covers the syntax, the months argument, and the common due-date patterns built on top of it.
EOMONTH syntax
=EOMONTH(start_date, months) | Argument | Required | What it does |
|---|---|---|
start_date | Required | The reference date. EOMONTH only uses its month and year to find the month-end. |
months | Required | How many months to move before (negative) or after (positive) the start_date. Use 0 for the current month. |
result | Optional | Returns a serial date number for the last day of that month, which you format as a date. |
Note: Add 1 to get the first day of the next month: =EOMONTH(A2, 0)+1. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Last day of this month
=EOMONTH(A2, 0) With months set to 0, EOMONTH returns the final day of A2's own month. Format the result cell as a date so it does not show as a serial number.
2. Invoice due at end of next month
=EOMONTH(B2, 1) Moving forward 1 month and snapping to the end gives a clean end-of-next-month due date, a very common net-payment term.
3. First day of the current month
=EOMONTH(C2, -1)+1 EOMONTH(C2, -1) is the last day of the previous month; adding 1 lands on the first day of the current month, a reliable month-start formula.
4. Last day three months ago
=EOMONTH(D2, -3) A negative months value moves backward. This returns the final day of the month three months before D2, handy for trailing-quarter reports.
How to write EOMONTH step by step
- 1
Click the cell where you want the date and type =EOMONTH(
- 2
Click the cell with your reference date (start_date), then type a comma.
- 3
Type how many months to shift: 0 for this month, 1 for next month, -1 for last month.
- 4
Close the bracket ) and press Enter.
- 5
If the result shows a number, format the cell as a date (Home tab, Number group).
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#NUM! | The resulting date falls outside the range Excel can represent, usually from an extreme months value. | Use a realistic months offset so the result stays within valid date limits. |
#VALUE! | The start_date is text rather than a real date, or months is not a number. | Make sure start_date is a genuine date and months is a whole number, not text. |
Shows a number, not a date | EOMONTH returns a date serial number and the cell is formatted as General. | Select the cell and apply a date format from the Home tab Number dropdown. |
#NAME? | EOMONTH is mistyped. | Type the name exactly as EOMONTH. In very old Excel versions it required the Analysis ToolPak add-in. |
Frequently asked questions
How do I get the first day of a month with EOMONTH?
Take the previous month-end and add one day: =EOMONTH(date, -1)+1. This returns the first day of the same month as your reference date.
What does the months argument do?
It shifts the result by that many months before or after start_date. Use 0 for the same month, 1 for the next month, -1 for the previous month, and so on. EOMONTH always returns the last day of the resulting month.
Why does EOMONTH show a number instead of a date?
EOMONTH returns a date serial number. The cell just needs a date format applied from the Home tab, after which it displays as a normal date.
Does EOMONTH work in Google Sheets?
Yes. Google Sheets supports EOMONTH with the same start_date and months arguments and identical behavior.