# How to count dates that fall on a weekend in Excel

Use WEEKDAY with return type 2, where Saturday is 6 and Sunday is 7: `=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))`.

**Excel:** `=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Ticket | Opened |  |  | Weekend tickets |
| T-801 | 2026-03-02 |  |  |  |
| T-802 | 2026-03-06 |  |  |  |
| T-803 | 2026-03-07 |  |  |  |
| T-804 | 2026-03-08 |  |  |  |
| T-805 | 2026-03-11 |  |  |  |
| T-806 | 2026-03-14 |  |  |  |
| T-807 | 2026-03-17 |  |  |  |
| T-808 | 2026-03-20 |  |  |  |
| T-809 | 2026-03-22 |  |  |  |
| T-810 | 2026-03-26 |  |  |  |

Formula in F1: `=SUMPRODUCT(--(WEEKDAY(B2:B11,2)>5))`

Result: 4

## 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.

## Other ways

- `=SUMPRODUCT((WEEKDAY(B2:B11)=1)+(WEEKDAY(B2:B11)=7))`: 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))`: 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 `>5` picks 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-weekend-dates