WEEKDAY and EOMONTH

About this lesson

WEEKDAY returns which day of the week a date falls on, as a number. EOMONTH returns the last day of a month a given number of months away.

By the end you can

find the day of the week with WEEKDAY and flag weekends.
find the last day of any month with EOMONTH.

Practises WEEKDAY EOMONTH DATE TEXT

By Ali, Founder, Excel Lessons

The idea

WEEKDAY's second argument is the one that matters. Left out, it counts Sunday as day 1. That is the American convention, and almost never what a British sheet wants. Pass 2 and Monday is 1 through Sunday 7. That makes a weekend test as simple as asking whether the number is above 5. Get into the habit of writing it even when the answer looks right. The wrong numbering is off by exactly one day and looks completely normal.

EOMONTH exists because months are not the same length. "The end of next month" cannot be reached by adding 30 days. Its second argument, 0 for this month, 1 for next, -1 for last, handles leap years and 31-day months without anybody thinking about it. Invoicing, payroll and reporting periods almost all run to month ends. So it turns up more often than you would expect.

The mistake to watch for

Leaving out WEEKDAY's second argument. The default counts Sunday as day 1. So a weekend test written for Monday-first numbering is off by exactly one day and looks completely normal. Write the 2 even when the answer seems right. And never add 30 days to reach the end of next month. That is what EOMONTH is for.

Where this comes up again

Grouping by Month: EOMONTH and SUMIFS Dates in Practice
TEXT and VALUE: Converting Between the Two Text: Building and Cleaning
Building and Cleaning: Putting It Together Text: Building and Cleaning
Working Days: NETWORKDAYS and WORKDAY Dates in Practice

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

WEEKDAY returns the day of the week as a number.

In `C2`, type: `=WEEKDAY(B2,2)`. 2 makes Monday day 1.

Row	A	B	C	D
1	Delivery	Date	Weekday	Month end
2	Pallet A	45365
3	Pallet B	45367
4	Pallet C	45332
5
6	Feb 2024	45332
7
8

Every step

1

WEEKDAY returns the day of the week as a number. In C2, type: =WEEKDAY(B2,2). 2 makes Monday day 1.

Hint. Two arguments, and the second one matters.
2

Leave the second argument out and the answer changes. Read =WEEKDAY(B2) and say what it returns.

Hint. Which day does the default call day 1?
3

In C4, say if B4 falls at the weekend. Shape: =WEEKDAY(cell,2)>5. It returns TRUE or FALSE.

Hint. Saturday is the sixth day of the week.
4

EOMONTH gives the last day of a month. In D4, find the last day of B4's month. Shape: =EOMONTH(cell,0).

Hint. Zero means stay here.
5

B6 is a date in February 2024. Read =TEXT(EOMONTH(B6,1),"d mmmm yyyy") and say what it returns.

Hint. One month on from February.