Excel Challenge 19 : Highlight Weekend Shifts

What You Will Learn

Five shift dates in A2:A6. You need to label each one as "Weekend" or "Weekday" in column C for payroll review.

Excel's WEEKDAY function turns a date into a number representing the day of the week. The trick is choosing the right numbering system — return type 2 makes Monday = 1, Tuesday = 2, ..., Sunday = 7. That way, any number greater than 5 is a weekend day.

=IF(WEEKDAY(A2,2)>5,"Weekend","Weekday")

Breaking it down:

WEEKDAY(A2,2) — returns the day number with Monday as 1. April 3 (Friday) = 5, April 4 (Saturday) = 6, April 5 (Sunday) = 7.
>5 — checks if the day is Saturday (6) or Sunday (7).
IF(... ,"Weekend","Weekday") — shows "Weekend" for Saturday/Sunday, "Weekday" for everything else.

In this schedule: April 3 (Fri) and April 7 (Tue) are weekdays, April 5 (Sun) and April 6 (Mon) are weekdays, April 4 (Sat) is a weekend. That gives 2 weekend shifts and 3 weekday shifts.

The return type matters. Without the ,2 argument, WEEKDAY uses the default where Sunday = 1 and Saturday = 7. The >5 check would still work but Sunday would be 1 (not >5), causing incorrect results. Return type 2 is the standard choice for Monday-start weeks.

WEEKDAY with type 2 makes weekend detection as simple as >5.

The audit formulas:

B9: =COUNTA(A2:A6) — counts total shift records.
B10: =COUNTIF(C2:C6,"Weekend") — counts weekend shifts.

Alternative: TEXT for Readability

If you want to show the actual day name instead of "Weekend"/"Weekday" for display purposes:

=TEXT(A2, "dddd")

This returns "Saturday", "Sunday", etc. It does not classify weekends automatically but makes the raw date easier to read.

In this dataset, April 4 (Saturday) returns 6 with type 2 — classified as Weekend. April 5 (Sunday) returns 7 — also Weekend. April 6 (Monday) returns 1 — classified as Weekday. Without the ,2 argument, Sunday would return 1 (not >5) and would be incorrectly classified as Weekday.

The return type is easy to forget but changes everything. Always check which number represents Sunday in your chosen system before writing the comparison.