Last day of the month and day of the week in Excel
EOMONTH gives the last day of any month; WEEKDAY gives the day of the week as a number. Month-end reports, due dates and weekend checks, with examples.
=EOMONTH(A2,0) gives the last day of the month in A2.
=WEEKDAY(A2,2) gives the day of the week as a number, from 1 for
Monday to 7 for Sunday.
Both functions work in all current versions of Excel. They answer two questions that come up at work all the time. When does this month end? And does this date fall on a weekend?
The sheet
A small company pays its suppliers at the end of the month after the invoice. Nobody in the office works at the weekend. Here are 5 invoices, with the dates in column B.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Supplier invoices | ||||
| 2 | Invoice | Date | Day | Weekend | Due |
| 3 | 1041 | 9 Jan 2026 | 5 | FALSE | 28 Feb 2026 |
| 4 | 1042 | 31 Jan 2026 | 6 | TRUE | 28 Feb 2026 |
| 5 | 1043 | 16 Feb 2026 | 1 | FALSE | 31 Mar 2026 |
| 6 | 1044 | 3 Mar 2026 | 2 | FALSE | 30 Apr 2026 |
| 7 | 1045 | 22 Mar 2026 | 7 | TRUE | 30 Apr 2026 |
Columns C, D and E are formulas. C3 holds =WEEKDAY(B3,2). D3
holds =WEEKDAY(B3,2)>5. E3 holds
=EOMONTH(B3,1). Each one is filled down to row 7.
EOMONTH: the last day of a month
EOMONTH takes a date and a number of months. It returns the last day of the month that many months away. The number is the part people forget, and it is not optional.
Here B5 is Monday 16 February 2026.
-
=EOMONTH(B5,0)gives 28 February 2026, the end of this month. -
=EOMONTH(B5,1)gives 31 March 2026, the end of next month. -
=EOMONTH(B5,-1)gives 31 January 2026, the end of last month.
Add 1 and you get the first day of the next month.
=EOMONTH(B5,-1)+1 gives 1 February 2026, the first day of
B5's own month. =EOMONTH(B5,0)+1 gives 1 March 2026.
EOMONTH knows leap years. =EOMONTH(DATE(2028,2,10),0) gives 29
February 2028. You never have to think about it.
If E3 shows 46081: that is the date as a number. EOMONTH returns a serial number, and the cell may not have a date format yet. Press Ctrl+Shift+3 to show it as a date. The dates are numbers article explains why.
Why not add 30 days?
Because months are not 30 days long. Invoice 1042 is dated Saturday 31 January 2026. Its payment is due at the end of February.
=B4+30 gives Monday 2 March 2026. It jumps over the end of
February. A payment made on that date is 2 days late.
=EOMONTH(B4,1) gives Saturday 28 February 2026. That is the
date the terms mean.
Adding 30 days gives a date near the end of the month in some months, and not in others. Use EOMONTH for any rule that says "end of month".
WEEKDAY: the day of the week as a number
WEEKDAY turns a date into a number from 1 to 7. The second argument, the return type, decides which day is 1. Here is one week in March 2026 with both numbering systems.
| Date | =WEEKDAY(date) | =WEEKDAY(date,2) |
|---|---|---|
| Monday 2 March | 2 | 1 |
| Tuesday 3 March | 3 | 2 |
| Wednesday 4 March | 4 | 3 |
| Thursday 5 March | 5 | 4 |
| Friday 6 March | 6 | 5 |
| Saturday 7 March | 7 | 6 |
| Sunday 8 March | 1 | 7 |
With no second argument, the return type is 1. Sunday is 1 and Saturday is 7. With 2, Monday is 1 and Sunday is 7. So the weekend is always 6 and 7, and one test finds it.
=WEEKDAY(B7,2)>5 gives TRUE for Sunday 22 March. It gives
FALSE for Friday 9 January.
The mistake: leaving out the 2
Someone writes =WEEKDAY(B3)>5. It looks the same as the test
above, and it gives no error. But with return type 1, 6 is Friday and 7 is Saturday.
- Row 3, Friday 9 January: WEEKDAY gives 6, so the test says TRUE. A Friday is flagged as a weekend.
- Row 7, Sunday 22 March: WEEKDAY gives 1, so the test says FALSE. A Sunday is missed.
The answers are wrong by exactly one day, and they look normal. Always write the return type.
If you must use type 1, the weekend test is
=OR(WEEKDAY(B3)=1,WEEKDAY(B3)=7). That is longer, and easier to
get wrong.
Both together: a due date on a Saturday
Look at E3 and E4. Both invoices are due on Saturday 28 February 2026. The office is closed, so the payment has to go out on the Friday before.
Spot it:
=IF(WEEKDAY(EOMONTH(B3,1),2)>5,"Weekend","OK") gives
Weekend.
Move it: =WORKDAY(EOMONTH(B3,1)+1,-1) gives
Friday 27 February 2026.
The formula goes to the first day of the next month. Then WORKDAY steps back 1 working day. On a weekday month end, like Tuesday 31 March, it gives the month end itself.
That formula is the last working day of a month. Payroll and month-end reports use it a lot. WORKDAY can also skip public holidays. The working days article shows how.
The name of the day: TEXT
A number from 1 to 7 is good for tests. For people, a name is better.
=TEXT(B3,"dddd") gives Friday.
=TEXT(B3,"ddd") gives Fri.
The result is text, not a date. You cannot add days to it, and it sorts from A to Z, so Friday comes before Monday. Use it for labels, and keep the real date in its own column.
You can also keep the date and only change how it looks. Select the cell and press Ctrl+1. Choose Custom and type dddd as the format. The cell shows Friday but still holds the date. That is the better choice when you still need to calculate with it.
What I would put in a real file
-
A due date column with
=EOMONTH(date,1), never a date plus 30. -
A weekend check with
=WEEKDAY(date,2)>5, with the 2 always written. - Day names from a custom format, so the column is still made of dates.
The lessons below use the same two functions on a real sheet. You type each formula, and your answer is checked when you press Enter.