WEEKDAY converts a date into a day-of-week number. The optional type controls the numbering: Sunday-first, Monday-first or a zero-based Monday week. Set it explicitly when building weekend checks.
WEEKDAY function syntax
=WEEKDAY(date, [type])- date: a cell holding a numeric date, a DATE result or a date serial number.
- type 1 or omitted: Sunday=1 through Saturday=7.
- type 2: Monday=1 through Sunday=7. Type 3: Monday=0 through Sunday=6.
- Types 11 through 17 provide other week starts; type 11 starts Monday, 12 Tuesday, through 17 Sunday, each numbered 1 to 7.
Set up the example data
Enter this dataset starting in A1. The first row contains headings. Keep the result area separate from the source table.
| Date |
|---|
| =DATE(2024,1,15) |
| =DATE(2024,1,20) |
| =DATE(2024,1,21) |
| =DATE(2024,2,29) |
Use the default Sunday-first numbering
Enter this formula in A8. January 15, 2024 is Monday, so the default numbering returns 2. Sunday would return 1 and Saturday 7.
=WEEKDAY(A2)Result: 2.
Make Monday day 1
With type 2, the same Monday returns 1. Saturday and Sunday become 6 and 7, which is useful when a schedule uses Saturday and Sunday as its weekend.
=WEEKDAY(A2,2)Result: 1.
Use zero-based weekday numbers
Type 3 returns 0 for Monday. Its highest result is 6 for Sunday, so WEEKDAY does not always return a number from 1 through 7.
=WEEKDAY(A2,3)Result: 0.
Enter a date without ambiguous text
DATE builds February 29, 2024 as a numeric date. WEEKDAY returns 5 under the default system because the leap day is Thursday.
=WEEKDAY(DATE(2024,2,29))Result: 5.
Flag Saturday and Sunday
The date in A3 is Saturday. Under type 2 its value is 6, so >5 is TRUE. This rule assumes a Saturday/Sunday weekend and does not identify public holidays.
=WEEKDAY(A3,2)>5Result: TRUE.
Highlight weekend rows without shifting the date column
For a schedule starting in row 2, apply this custom conditional-formatting formula to A2:B5. The dollar sign fixes the date column while the row changes for each record.
=IF(ISNUMBER($A2),WEEKDAY($A2,2)>5,FALSE)Result: FALSE.
Return a day-name label
CHOOSE maps the default 1-to-7 numbering to the listed labels. For the Monday date in A2, it returns Mon. Keep the labels in Sunday-first order for this version.
=CHOOSE(WEEKDAY(A2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")Result: Mon.
Other Google Sheets articles you may also like