EOMONTH Function in Excel
Quick answer
EOMONTH returns the last day of a month, a set number of months before or after a date you give it. Use 0 for the end of the same month, 1 for next month and -1 for last month. It’s the go-to for payment due dates, month-end reporting and BAS quarter ends.
=EOMONTH(start_date, months)Works in: Excel 365, Excel 2024, 2021, 2019, 2016, Excel for the web and Google Sheets. It’s been around for years, so it works in older versions too.
Syntax
=EOMONTH(start_date, months)| Argument | Required? | What it does |
|---|---|---|
start_date | Yes | The date you’re starting from. Point it at a cell holding a real date, or build one with DATE. |
months | Yes | How many months to move before finding the month end. 0 is the same month, positive numbers go forward, negative numbers go back. Decimals are truncated, so 1.9 acts as 1. |
How EOMONTH works
Think of EOMONTH as two steps. First it jumps forward or back by the number of whole months you ask for. Then it snaps to the last day of whatever month it lands in. The day in your start date doesn’t matter: 3 July and 28 July both give you 31 July.
Because it always lands on the real last day, it copes with 30-day months, 31-day months and leap years without you thinking about it. That’s why it beats typing =A2+30 for anything tied to the calendar.
EOMONTH returns a date serial number, the same kind of number Excel stores behind every date. Format the cell as a date and you’ll see it the way you expect. Add a number to the result and you get a date that many days into the next month.
Examples
All three examples use the Invoices sheet from the Bluegum Café workbook, with invoice dates in column B. I’ve added two helper columns: Due Date in H and Quarter End in I.
| Invoice No | Date | Supplier | Total | Due Date (H) | Quarter End (I) |
|---|---|---|---|---|---|
| INV-1001 | 03/07/2025 | Ridgeline Roasters | $528.00 | 30/08/2025 | 30/09/2025 |
| INV-1002 | 04/07/2025 | Coastal Dairy Co | $237.05 | 30/08/2025 | 30/09/2025 |
Basic: Find the end of the invoice month
On the Invoices sheet, the invoice date is in B2. Type this formula in any empty cell, say J2:
=EOMONTH(B2,0)With 03/07/2025 in B2, the result is 31/07/2025. The 0 means “stay in this month”.
Real-world: Work out a “30 days EOM” due date
Plenty of Aussie suppliers put “30 days EOM” on their invoices, meaning payment is due 30 days after the end of the month the invoice is dated. On the Invoices sheet, the date is in B2 and the formula goes in H2:
=EOMONTH(B2,0)+30With 03/07/2025 in B2, the result is 30/08/2025. Copy it down and INV-1002 (04/07/2025) lands on the same due date, which is exactly how EOM terms work.
Advanced: Find the BAS quarter end for each invoice
Quarterly BAS periods end on 30 September, 31 December, 31 March and 30 June. To tag each invoice with its quarter, combine EOMONTH with MONTH and MOD. On the Invoices sheet, the date is in B2 and the formula goes in I2:
=EOMONTH(B2,MOD(3-MONTH(B2),3))MOD(3-MONTH(B2),3) works out how many months are left until the next quarter-end month (0, 1 or 2), and EOMONTH snaps to the end of it. With 03/07/2025 in B2, the result is 30/09/2025, the end of Q1 FY26.
Guru Tip
If EOMONTH gives you something like 45869 instead of a date, the formula is fine. That’s the serial number for 31/07/2025, and the cell just isn’t formatted as a date. Go to Home > Number Format and pick Short Date. I format the whole column before I write the formula so I never have to think about it.
Common errors
#VALUE!: the start date is text Excel can’t read
Cause: start_date is text that doesn’t look like a date, which often happens with dates pasted in from a bank or POS export. Fix: Convert the column to real dates with Data > Text to Columns (choose DMY in the last step), or build the date with DATE.
#NUM!: the result isn’t a valid date
Cause: start_date plus months lands somewhere Excel can’t store as a date, such as before 1900, or start_date is a negative number. Fix: Check the start date cell holds a real date and that months isn’t a huge negative number pulled in by mistake.
Grab the template: EOMONTH
Get the Bluegum Café invoice sheet with EOM due dates and BAS quarter ends already set up, so you can drop in your own bills.