Challenge Scenario
In shift scheduling and payroll processing, employees who work on weekend shifts (Saturday and Sunday) are entitled to special overtime pay differentials and weekend allowances.
Using Excel's date functions, payroll administrators can automatically detect day-of-week numbers from date values and flag weekend shifts for compensation adjustments.
As a Payroll & Roster Coordinator, your tasks are:
- Day of Week (Column C): Extract the weekday number using the
WEEKDAY function (=WEEKDAY(B2)). In standard Excel mode, Sunday is 1 and Saturday is 7.
- Weekend Shift Flag (Column D): If Day of Week is 1 (Sunday) or 7 (Saturday) (
OR(C2=1, C2=7)), assign "WEEKEND"; otherwise assign "WEEKDAY".
- Total Shifts (Cell B8): Count total shift logs using
COUNTA(A2:A5).
- Weekend Shift Count (Cell C8): Count how many shifts earned the
"WEEKEND" flag using COUNTIF.
Your Sheet Layout
The payroll shift schedule is structured into clear operational sections:
- Shift Logs (Columns A & B): Records employee names and their assigned shift calendar dates.
- Weekday & Weekend Classification (Columns C & D): Calculates the day index (1 to 7) and outputs the payroll shift classification (
WEEKEND vs WEEKDAY).
- Roster Summary (Rows 7 & 8): Tally of total shifts worked and count of weekend penalty rate shifts.
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:B5 |
Employee, Shift Date |
Shift Logs |
Employee names and work dates |
Read-only shift attendance records. |
C2:C5 |
Day of Week |
Calculation |
=WEEKDAY(B2) |
Returns weekday index: 1 (Sun), 2 (Mon), ..., 7 (Sat). |
D2:D5 |
Shift Type |
Classification |
=IF(OR(C2=1, C2=7), "WEEKEND", "WEEKDAY") |
Flags Sunday (1) and Saturday (7) shifts as WEEKEND. |
B8 |
Total Shifts |
Summary |
=COUNTA(A2:A5) |
Counts total recorded work shifts (4). |
C8 |
Weekend Shifts |
Summary |
=COUNTIF(D2:D5, "WEEKEND") |
Counts total weekend shifts requiring overtime rates (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column C (C2:C5), output "Weekend" if WEEKDAY(A2, 2) > 5, otherwise "Weekday".
2
In cell B8, count total shifts using COUNTA(A2:A5).
3
In cell C8, count "Weekend" shifts using COUNTIF(C2:C5, "Weekend").
4
In cell D8, count "Weekday" shifts using COUNTIF(C2:C5, "Weekday").
Solution & Formula Breakdown
Let's examine how the date and logical functions identify weekend days:
1. Extracting Day Numbers with WEEKDAY (C2:C5)
=WEEKDAY(B2)
The WEEKDAY(serial_number) function returns an integer from 1 (Sunday) through 7 (Saturday):
- Row 2 (2026-09-05, Saturday):
WEEKDAY("2026-09-05") returns 7.
- Row 3 (2026-09-07, Monday):
WEEKDAY("2026-09-07") returns 2.
- Row 4 (2026-09-09, Wednesday):
WEEKDAY("2026-09-09") returns 4.
- Row 5 (2026-09-13, Sunday):
WEEKDAY("2026-09-13") returns 1.
2. Flagging Weekend Shifts with IF and OR (D2:D5)
=IF(OR(C2=1, C2=7), "WEEKEND", "WEEKDAY")
If the day index is either 1 (Sunday) or 7 (Saturday), the shift receives the "WEEKEND" label; otherwise, it is labeled "WEEKDAY".
3. Summary Metrics (B8 & C8)
- Total Shifts (Cell B8):
=COUNTA(A2:A5) returns 4 shifts.
- Weekend Shifts (Cell C8):
=COUNTIF(D2:D5, "WEEKEND") returns 2 weekend shifts.
By default in Excel, WEEKDAY(date) uses return type 1 (Sunday=1, Saturday=7). If you ever prefer Monday=1 through Sunday=7, you can pass return type 2: =WEEKDAY(date, 2).