EOMONTH function
EOMONTH returns the last day of a month, either the current one or several months away, as a date serial number. It is common in month-end reporting.
Example
Read it as a sentence: take January 15, 2024 and find the last day of the month 1 month later, which gives the date serial number 45351.
A
B
C
D
1
45351
2
3
Syntax
EOMONTH(start_date, months)
-
start_date(required) - A date in the starting month.
-
months(required) - Months to move; 0 means the same month, negative goes back.
Returns: The last day of the month, shifted by the given number of months.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=EOMONTH(DATE(2026,1,15),0) | 46053 | Same month |
=EOMONTH(DATE(2026,1,15),1) | 46081 | Next month |
=EOMONTH(DATE(2026,1,15),-1) | 46022 | Previous month |
=EOMONTH(DATE(2024,1,15),1) | 45351 | Leap February |
=EOMONTH(DATE(2026,1,15),1.9) | 46081 | Fractional months |
=EOMONTH(DATE(2026,1,15),{0,1,2}) | 46053 | Array months |
=EOMONTH("x",0) | #VALUE! | Bad date |
=EOMONTH(-1,0) | #NUM! | Negative serial |
Watch out for
- Returns a serial number; apply a date format.
- Use months of -1 then add 1 to get the first of the month.
- Text dates may fail depending on regional settings.
Where it works
- Excel
- EOMONTH is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Date and time functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .