The Problem
A raw date does not always tell you much at a glance. In a shift log, the real question is often whether that date lands on a weekend, because that can affect staffing reviews, overtime checks, or bonus pay.
That gets harder when the schedule is just a list of dates. People can recognize familiar dates for a week or two, but a longer roster needs the sheet to classify each row consistently so payroll does not depend on memory.
The flow below shows the schedule check. A shift date becomes a weekday number, the formula tests whether that number belongs to Saturday or Sunday, and the summary counts the weekend rows.
The Problem: Weekend Pay Starts with Date Classification Shift dates need to become clear weekend or weekday labels before payroll review.
In this workbook, each shift row has a date and employee name. The challenge is to classify the pay type for each row, then complete the audit section so the sheet shows total records and the number of weekend shifts.
- Each shift date needs a readable category.
- Saturday and Sunday should be treated as weekend shifts.
- The summary should count the weekend labels after classification.
That keeps payroll review simple. The reviewer can scan the pay type column instead of mentally converting every date into a day of week.
How We Solve It
WEEKDAY turns each date into a day number. With return type 2, Monday becomes 1 and Sunday becomes 7, which means weekend dates are easy to spot because they are greater than 5.
Method 1: Basic WEEKDAY output
Method 1: Return the day of week as a number.
The plain WEEKDAY function returns a numeric day code. That is useful for understanding how Excel reads the date, but for this worksheet we want a cleaner label in the final column.
This solves the first part of the problem by translating the raw date into something Excel can test. Once the date becomes a weekday number, the sheet can decide whether that shift falls on a weekend or a normal workday.
=WEEKDAY(A2)
Method 2: WEEKDAY with return type 2
Method 2: Start the week on Monday so weekend days become 6 and 7.
This is the approach the validator expects. Once Monday is treated as 1, any result above 5 is a Saturday or Sunday. Wrapping that check in IF gives us the readable status we want.
This solves the full challenge because the row only needs one final label in column C. By using return type 2, the weekend rule becomes simple and the formula can classify each shift without extra helper columns.
=IF(WEEKDAY(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),2)>5,"Weekend","Weekday")
Method 3: Convert the date to text
Method 3: Show the full day name when the list needs to be easier to read.
If the sheet is meant for people who do not want day numbers at all, TEXT can display the weekday name directly. That is not required for this challenge, but it is a useful presentation step after the classification is done.
This solves the readability side of the problem. It does not replace the weekend test, but it makes the schedule easier to review when people want to see Saturday or Sunday instead of a day number.
=TEXT(A2, "dddd")
Function Explanation
1. WEEKDAY
WEEKDAY returns the day of week for a date. The return type matters because it changes which number represents each day.
In this workbook, the dates are stored as text, so the solution rebuilds a real date first. Once the date is valid, return type 2 makes weekend detection simple.
2. IF
IF converts the day check into a category label. That is what makes the result useful for people reading the sheet instead of reading formula logic.
The label matters because payroll and staffing reviews usually need a clear category, not a weekday number.
Learn more this functionIF
3. COUNTIF
COUNTIF counts how many rows ended up marked as Weekend. That gives us the summary total without another manual scan.
That final count helps the reviewer understand how many shifts may need special handling.
The biggest thing to watch with weekday formulas is the numbering system you choose. The formula can be correct and still give the wrong category if the return type does not match the logic you wrote around it.
Classify each shift date as a weekend or weekday, then finish the summary so the sheet shows the total number of shift records and how many of them fall on the weekend.