EOMONTH Function in Excel, Google Sheets, Airtable & Notion
EOMONTH returns the last day of the month that is a given number of months before or after a date. Use 0 for the end of the current month.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | EOMONTH(start_date, months) |
| Google Sheets | ✔ Built in | EOMONTH(start_date, months) |
| Airtable | ≈ Workaround | DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(date, months + 1, 'months'), 'YYYY-MM-01')), -1, 'days')No EOMONTH: jump to the following month, take its 1st and step back one day. |
| Notion | ≈ Workaround | let(m, dateAdd(date, months + 1, "months"), dateSubtract(m, date(m), "days"))No end-of-month function: go to the following month and subtract its day number, which lands on the last day of the month before. |
| Excel | =EOMONTH(DATE(2024, 1, 31), 1) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =EOMONTH(DATE(2024, 1, 31), 1) | open sheets.new ↗ |
| Airtable workaround | DATEADD(DATETIME_PARSE(DATETIME_FORMAT(DATEADD(DATETIME_PARSE("2024-01-31", "YYYY-MM-DD"), 2, 'months'), 'YYYY-MM-01'), 'YYYY-MM-DD'), -1, 'days') | |
| Notion workaround | let(m, dateAdd(parseDate("2024-01-31"), 2, "months"), dateSubtract(m, date(m), "days")) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Arguments
| Argument | What it means |
|---|---|
start_date | Any date in the starting month. Only its year and month matter. |
months | 0 = this month, 1 = next month, โ1 = last month. Decimals are truncated. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=EOMONTH(DATE(2024, 1, 31), 1) | 2024-02-29 | End of the next month; 2024 is a leap year. | |
=EOMONTH(DATE(2023, 1, 15), 1) | 2023-02-28 | ||
=EOMONTH(DATE(2024, 3, 15), 0) | 2024-03-31 | 0 = end of the same month. | |
=EOMONTH(DATE(2024, 3, 15), -1) + 1 | 2024-03-01 | End of last month plus one day is the first of this month. |
Examples use Excel / Google Sheets syntax unless noted.
How EOMONTH works
EOMONTH is the standard tool for month-end reporting, invoice due dates (“end of next month” is =EOMONTH(A2, 1)) and grouping transactions by month. Because the result is always a month end, it makes a clean grouping key, and =EOMONTH(A2, -1) + 1 gives the first of the month. =DAY(EOMONTH(A2, 0)) is the number of days in the month.
Unlike EDATE, the day of start_date is ignored, so there is no clamping to worry about. Like EDATE, Excel returns a serial number that needs a date format. The same result can be written as =DATE(YEAR(A2), MONTH(A2) + 1, 0), because day 0 rolls back to the last day of the previous month.
Airtable and Notion have no EOMONTH. Both copy-ready formulas jump to the month after the target month and step back: Airtable formats that date as the 1st and subtracts a day, Notion subtracts the date's own day number (31 March − 31 days = 29 February). Adding months first and then stepping back is safe even from the 31st, because DATEADD and dateAdd stay inside the target month.
Frequently Asked Questions
- How do I get the first day of the month?
- Use
=EOMONTH(A2, -1) + 1, or=DATE(YEAR(A2), MONTH(A2), 1). - How many days are in a month?
=DAY(EOMONTH(A2, 0)).