Excel Practice
Lessons Lesson 79

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

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

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

Which day of the week is the first delivery? Put the weekday number in C2, counting Monday as day 1.

To begin, type it exactly:

=WEEKDAY(B2,2)

C2
Row A B C D E F G
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. 1

    Which day of the week is the first delivery? Put the weekday number in C2, counting Monday as day 1. To begin, type it exactly: =WEEKDAY(B2,2).

    Hint. Two arguments, and the second one matters.

  2. 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. 3

    In C4, say whether the third delivery falls at the weekend, counting Monday as day 1. TRUE or FALSE.

    Hint. Saturday is 6.

  4. 4

    EOMONTH gives the last day of a month. In D4, get the last day of the month the third delivery falls in.

    Hint. Zero means stay here.

  5. 5

    Read =TEXT(EOMONTH(B6,1),"d mmmm yyyy") and say what the end of the month after February 2024 is.

    Hint. One month on from February.