EOMONTH Function in Excel and Google Sheets
Purpose
Return the last day of the month that is a specified number of months before or after a given date
Return value
A date serial number representing the last day of the target month, displayed as a date when the cell is formatted as a date
EOMONTH is the date function that handles the most annoying problem in date arithmetic: finding month-end dates when months have different lengths. January has 31 days, February has 28 or 29, April has 30 -- manually checking which day is last in any given month is tedious and error-prone. EOMONTH returns the correct last day automatically, including leap years, for any month you point it at. Set the months argument to 0 for the current month's end, 1 for next month's end, -1 for last month's end. Use it to calculate contract end dates that align with month-end, invoice due dates that fall on the last day of a month, or quarter-end dates for financial reporting. Adding 1 to the result gives you the first day of the following month, making EOMONTH the foundation for every first-and-last-of-month calculation I do.
Syntax
✓ Excel 2007+
✓ Google Sheets
= Same syntax
=EOMONTH(start_date, months)
Arguments
| Argument | Required | Description |
|---|---|---|
| start_date | Required | The reference date. Can be a cell reference containing a date, a DATE formula, or a date entered as text that Excel can parse. |
| months | Required | The number of months to offset from start_date. 0 returns the last day of start_date's month. Positive values go forward, negative values go backward. Non-integer values are truncated to integers. |
How it works
EOMONTH returns a date serial number -- format the result cell as a date to display it correctly. The months argument shifts the target month: 0 stays in the same month as start_date, 1 moves to the next month, -1 moves to the previous month, and so on. The returned date is always the final day of the target month, so EOMONTH correctly handles 28, 29, 30, and 31-day months without any extra logic. To get the first day of a month, add 1 to the previous month's EOMONTH: =EOMONTH(A2,-1)+1 gives the first day of A2's month. This is cleaner than using DATE(YEAR,MONTH,1) because it chains naturally with the EOMONTH result. For quarter-end dates, target months 2, 5, 8, or 11 ahead of a quarter-start date. EOMONTH handles the add-1 pattern for month-start dates and is the foundation for most of the date range calculations in financial and operational reporting. The date function is the closest companion for building dates from components when EOMONTH alone is not sufficient.
Examples
1
Find the last day of each invoice's billing month so payment terms can be calculated from month-end.
| A | B | |
|---|---|---|
| 1 | Invoice Date | Month End |
| 2 | 2024-01-15 | =EOMONTH(A2,0) |
| 3 | 2024-02-08 | 2024-02-29 |
| 4 | 2024-03-22 | 2024-03-31 |
| 5 | 2024-04-05 | 2024-04-30 |
| 6 | 2024-12-01 | 2024-12-31 |
Row 2: 2024-01-31
— January 15 2024 is in January -- EOMONTH(date,0) returns the last day of January: 2024-01-31. For February 8, EOMONTH correctly returns 2024-02-29 because 2024 is a leap year.
The months argument of 0 means stay in the same month as the start date. Format the result column as a date -- without date formatting the result displays as a serial number. EOMONTH automatically handles different month lengths with no additional logic.
2
Find the first day of the following month for each date, using EOMONTH plus 1 as the cleanest approach.
| A | B | |
|---|---|---|
| 1 | Date | First of Next Month |
| 2 | 2024-01-15 | =EOMONTH(A2,0)+1 |
| 3 | 2024-02-08 | 2024-03-01 |
| 4 | 2024-03-22 | 2024-04-01 |
| 5 | 2024-11-05 | 2024-12-01 |
| 6 | 2024-12-20 | 2025-01-01 |
Row 2: 2024-02-01
— EOMONTH(Jan 15, 0) returns Jan 31. Adding 1 gives Feb 1 -- the first day of the following month. December 20 correctly rolls over to January 1 2025.
Adding 1 to any EOMONTH result gives the first day of the next month. This works across year boundaries -- December's last day plus 1 always gives January 1 of the following year with no extra logic needed.
3
Calculate the end date of each quarter by finding the last day of the month 2 months after the quarter-start date.
| A | B | |
|---|---|---|
| 1 | Quarter Start | Quarter End |
| 2 | 2024-01-01 | =EOMONTH(A2,2) |
| 3 | 2024-04-01 | 2024-06-30 |
| 4 | 2024-07-01 | 2024-09-30 |
| 5 | 2024-10-01 | 2024-12-31 |
| 6 | 2025-01-01 | 2025-03-31 |
Row 2: 2024-03-31
— January 1 plus 2 months = March. EOMONTH returns the last day of March: 2024-03-31. Q2 start April 1 plus 2 months gives June 30, Q3 gives September 30, Q4 gives December 31.
EOMONTH(quarter_start, 2) gives the last day of the third month of the quarter -- always the correct quarter-end date. This is how I build financial reporting date ranges without hardcoding any month lengths.
Common use cases
1. Find the last day of each invoice or billing month for month-end payment terms
=EOMONTH(A2,0)
2. Calculate subscription or contract end dates that always fall on a month-end
=EOMONTH(A2,11)
3. Get the first day of the current month for a reporting period start
=EOMONTH(A2,-1)+1
4. Calculate quarter-end dates for financial reporting without hardcoding month lengths
=EOMONTH(A2,2)
5. Find the last working day of the month by combining EOMONTH with WORKDAY
=WORKDAY(EOMONTH(A2,0)+1,-1)
Common errors
Result displays as a number instead of a date
The result cell is not formatted as a date. EOMONTH returns a serial number that looks like a large integer without date formatting.
Fix: Select the result column, press Ctrl+1, and choose a Date format. Or format the column as a date before entering the formula.
#VALUE! error
The start_date argument contains a text string that Excel cannot interpret as a date, or months is text rather than a number.
Fix: Ensure start_date is a genuine date value -- use DATEVALUE to convert text dates to real dates first.
Result is one day earlier than expected
You used EOMONTH for the previous month (+1 pattern) but forgot to add 1. =EOMONTH(A2,-1) returns the last day of the previous month, not the first day of the current month.
Fix: Add 1: =EOMONTH(A2,-1)+1 gives the first day of A2's month.
Tips and variations
Use EOMONTH(date,-1)+1 for the first day of any month
This is the cleanest first-of-month formula -- cleaner than DATE(YEAR(A2),MONTH(A2),1) because it chains naturally from any EOMONTH calculation already in the formula. I use this pattern in every financial dashboard I build.
=EOMONTH(A2,-1)+1
Find the last working day of the month with WORKDAY and EOMONTH
WORKDAY(EOMONTH(A2,0)+1,-1) finds the last working day of A2's month -- it starts at the first day of the following month and goes back one working day. This is the standard pattern for any month-end payroll or payment date calculation.
=WORKDAY(EOMONTH(A2,0)+1,-1)
Use EOMONTH to set dynamic reporting period boundaries
Building a report that covers a specific month? Use =EOMONTH(TODAY(),-1)+1 as the period start and =EOMONTH(TODAY(),0) as the period end -- the report automatically updates to the current month every time the file opens.
=EOMONTH(TODAY(),0)
Excel vs Google Sheets
Excel vs Google Sheets
EOMONTH is built in from Excel 2007 onward and available in all versions of Google Sheets. In Excel 2003 it requires the Analysis ToolPak add-in. The syntax, leap year handling, and date serial number output are identical in Excel 2007+ and Google Sheets.
Frequently asked questions
EOMONTH returns the last day of a month that is a specified number of months before or after a given start date. =EOMONTH(A2,0) returns the last day of A2's month. =EOMONTH(A2,1) returns the last day of the following month. =EOMONTH(A2,-1) returns the last day of the previous month. It is most useful for calculating month-end deadlines and financial period end dates.
Use EOMONTH for the previous month and add 1: =EOMONTH(A2,-1)+1 returns the first day of A2's month. For the first day of the next month: =EOMONTH(A2,0)+1. This pattern is cleaner than using DATE(YEAR(A2),MONTH(A2),1) and is how I always derive month-start dates when I already have EOMONTH in the formula.
The months argument offsets the result by that many months. 0 means the same month as the start date. Positive numbers move forward -- 1 means next month, 3 means three months ahead. Negative numbers move backward -- -1 means last month, -12 means twelve months ago. The result is always the last day of the target month, automatically accounting for different month lengths and leap years.
Yes. EOMONTH correctly returns February 29 for leap years and February 28 for non-leap years without any manual calculation. =EOMONTH(DATE(2024,1,1),1) returns 2024-02-29 because 2024 is a leap year. =EOMONTH(DATE(2023,1,1),1) returns 2023-02-28. This automatic handling of variable month lengths is the primary advantage over manually calculating month-end dates.
EOMONTH requires the Analysis ToolPak add-in in Excel 2003 and earlier. From Excel 2007 onward it is a built-in function requiring no add-in. It is available in all versions of Google Sheets. In modern Excel and Google Sheets use it freely without checking for the add-in.