How to count dates that fall on a weekend in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))Use WEEKDAY with return type 2, where Saturday is 6 and Sunday is 7: =SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5)).
- Result on the example
- 4 in
F1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in F1. Change any input and the result updates, or edit the formula to experiment.
How it works
WEEKDAY(date,2) numbers the days Monday = 1 through Sunday = 7, so the weekend is everything above 5. Given the whole column, WEEKDAY returns one number per date, the comparison turns them into TRUE or FALSE, and SUMPRODUCT counts the TRUEs.
March 7 and 14 are Saturdays and March 8 and 22 are Sundays, so 4 tickets came in at the weekend.
=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))Result4
B2:B1110 rows × 1 col: Mar 2, 2026, Mar 6, 2026, Mar 7, 2026, Mar 8, 2026, …A range: 10 cells in one column.
WEEKDAY(B2:B11,2){10 rows × 1 col} 1, 5, 6, 7, …WEEKDAY: Day of the week as a number; Sunday = 1 by default.- serial_number
B2:B1110 rows × 1 col: Mar 2, 2026, Mar 6, 2026, Mar 7, 2026, Mar 8, 2026, … - return_type
2
- serial_number
WEEKDAY(B2:B11,2)>5{10 rows × 1 col} FALSE, FALSE, TRUE, TRUE, …The > operator tests greater than.
-(WEEKDAY(B2:B11,2)>5{10 rows × 1 col} 0, 0, -1, -1, …Makes the value negative.
--(WEEKDAY(B2:B11,2)>5{10 rows × 1 col} 0, 0, 1, 1, …Makes the value negative.
SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))4SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--(WEEKDAY(B2:B11,2)>5{10 rows × 1 col} 0, 0, 1, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5)). We ran it in Google Sheets mode on the same data and it returns 4.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT((WEEKDAY(B2:B11)=1)+(WEEKDAY(B2:B11)=7)) | 4 | Uses the default numbering, where Sunday is 1 and Saturday is 7. Adding the two tests is OR logic. |
| =SUMPRODUCT(--(WEEKDAY(B2:B11,2)<6)) | 6Different result | Counts weekdays instead: Monday to Friday. Counts the 6 tickets opened on weekdays. |
Common mistakes
- Without the 2, WEEKDAY starts the week on Sunday = 1, and
>5picks Friday and Saturday instead of the weekend. - COUNTIF can't do this, because its criteria can't call WEEKDAY on each cell. That is why the answer uses SUMPRODUCT.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Count dates that fall on a weekend
F1, count the tickets in B2:B11 that were opened on a Saturday or Sunday.Questions
How do I count weekend dates between two dates?
Add the date range to the product: =SUMPRODUCT((WEEKDAY(B2:B11,2)>5)*(B2:B11>=E1)*(B2:B11<=E2)).
My weekend is Friday and Saturday. What changes?
Test for days 5 and 6, Friday and Saturday in the Monday = 1 numbering: =SUMPRODUCT((WEEKDAY(B2:B11,2)=5)+(WEEKDAY(B2:B11,2)=6)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.