EOMONTH Function
Date & Time
Get the last day of any month, the first day of a month and a monthly timeline that never needs retyping.
Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards
EOMONTH stands for end of month. Give it a date and a number of months, and it returns the last day of the month that many months away. That sounds narrow, but month ends are everywhere in a small business: invoice due dates, monthly reports, subscription periods and the column headers of every monthly model. EOMONTH deals with 28, 30 and 31-day months and leap years so you do not have to.
In this guide
EOMONTH syntax
=EOMONTH(start_date, months)| Argument | Required? | What it does |
|---|---|---|
start_date |
Required | Any date. Only its month and year matter; the day is ignored. |
months |
Required | How many months to move. 0 is the same month, 1 is next month, -1 is last month. Decimals are truncated, so 1.9 counts as 1. |
With 15-Jan-26 in A2, here is what different values of months return:
| Formula | Result | Meaning |
|---|---|---|
=EOMONTH(A2,0) |
31-Jan-26 | End of this month |
=EOMONTH(A2,1) |
28-Feb-26 | End of next month (2026 is not a leap year) |
=EOMONTH(A2,-1) |
31-Dec-25 | End of last month |
=EOMONTH(A2,11) |
31-Dec-26 | End of the month 11 months on |
Note
Both arguments are required. Excel will not accept =EOMONTH(A2); write =EOMONTH(A2,0) for the end of the same month.
Month-end due dates
A brand design studio bills on terms of end of the following month: an invoice issued any time in January is due on the last day of February. That is EOMONTH(issue_date,1):
| A | B | C | ||
|---|---|---|---|---|
| 1 | Invoice | Issued | Due | |
| 2 | INV-101 | 08-Jan-26 | 28-Feb-26 | |
| 3 | INV-102 | 27-Jan-26 | 28-Feb-26 | |
| 4 | INV-103 | 03-Feb-26 | 31-Mar-26 | |
| 5 | INV-104 | 19-Mar-26 | 30-Apr-26 | |
| 6 | INV-105 | 30-Apr-26 | 31-May-26 |
Every invoice gets the last day of the following month, whether that month has 28, 30 or 31 days.
Change the 1 to 0 and the same formula gives the end of the current month, which is what month-to-date reports and accruals need.
Tip
28 February 2026 is a Saturday. For the last working day of the month instead, wrap it in WORKDAY: =WORKDAY(EOMONTH(B2,1)+1,-1) returns Friday 27 February. See NETWORKDAYS for how WORKDAY counts.
First day of a month
There is no start-of-month function, and you do not need one. The first day of a month is the day after the last day of the previous month:
=EOMONTH(A2,-1)+1First day of the month in A2. With 15-Jan-26, the result is 01-Jan-26.
=EOMONTH(A2,0)+1First day of next month: 01-Feb-26.
Adding 1 works because Excel stores dates as whole numbers, one per day, as explained below.
Tip
First-of-month dates are the safest way to group by month. A SUMIFS with ">="&first_day and "<="&EOMONTH(first_day,0) captures exactly one month, whatever its length.
Build a monthly timeline header
Forecasts and dashboards usually run months across the columns. Enter the first month once and let EOMONTH generate the rest, so changing one date shifts the whole timeline.
- In B1, enter the first month as a date, for example
=DATE(2026,1,1). - In C1, enter
=EOMONTH(B1,0)+1. It returns the first day of the following month. - Fill C1 to the right for as many months as you need.
- Select the row, press Ctrl + 1, pick Custom on the Number tab and type
mmm-yyin the Type box, so the headers read Jan-26, Feb-26 and so on.
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Jan-26 | Feb-26 | Mar-26 | Apr-26 | May-26 | Jun-26 | |
| 2 | Days | 31 | 28 | 31 | 30 | 31 | 30 |
Row 1 holds real dates formatted as mmm-yy. Row 2 counts the days in each month, covered in the next section.
If you prefer month-end headers, which suit balance sheets and cash positions, put the first month end in B1 and use =EOMONTH(B1,1) in C1 instead.
Tip
Because the headers are real dates, the formulas below them can use them directly as criteria. Text headers such as "Jan" cannot do that.
Days in a month
DAY returns the day number of a date. Apply it to a month end and you get the number of days in that month:
=DAY(EOMONTH(A2,0))31 for any date in January 2026, 28 for February 2026 and 29 for February 2028.
That is handy for daily averages and part-month charges. A coach with a $150 monthly membership can work out the charge for a client who joins partway through a month, with the start date in B2:
=150*(EOMONTH(B2,0)-B2+1)/DAY(EOMONTH(B2,0))With 20-Apr-26 in B2, the client pays $55 for the 11 days left in April.
EDATE: the same day, months later
EOMONTH always lands on a month end. When you need the same day of the month instead, such as a renewal date or the end of a contract, use its sibling EDATE. The syntax is =EDATE(start_date, months).
| Start | =EDATE(start,1) | =EOMONTH(start,1) |
|---|---|---|
| 15-Jan-26 | 15-Feb-26 | 28-Feb-26 |
| 31-Jan-26 | 28-Feb-26 | 28-Feb-26 |
| 30-Apr-26 | 30-May-26 | 31-May-26 |
When the day does not exist in the target month, EDATE uses that month's last day, so 31 January plus one month is 28 February.
Do not chain EDATE
Building a schedule with =EDATE(previous_cell,1) drifts: 31 January becomes 28 February, then 28 March, then 28 April. Always count from the original date, as in =EDATE($B$2,1), =EDATE($B$2,2) and so on.
Why EOMONTH can show a number like 46053
Excel stores every date as a serial number, counting 1 January 1900 as day 1. On that scale, 31 January 2026 is 46053. Formatting is what turns the number into something readable.
If an EOMONTH cell shows 46053 instead of a date, the formula is fine; the cell just has the General format. Fix it with any of these:
- Press Ctrl + Shift + # to apply the built-in date format.
- Choose Short Date from the number format box on the Home tab.
- Press Ctrl + 1 and set a custom format such as
dd-mmm-yyormmm-yy. The custom number formats guide lists the codes.
Note
Serial numbers are why date maths works: adding 1 moves forward a day, and subtracting one date from another gives the days between them. For working days rather than calendar days, see NETWORKDAYS.
Frequently asked questions
How do I get the first day of the month in Excel?
Use =EOMONTH(A2,-1)+1. It finds the last day of the previous month and adds one day.
Why does EOMONTH return a number instead of a date?
The result is a date serial number and the cell has the General format. Apply a date format with Ctrl+Shift+# or the number format box on the Home tab.
What is the difference between EOMONTH and EDATE?
EOMONTH always returns the last day of a month. EDATE keeps the same day number, and only falls back to the last day when that day does not exist in the target month.
Does EOMONTH handle leap years?
Yes. =EOMONTH(DATE(2028,2,1),0) returns 29 February 2028.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā