A 2024 workplace survey of 1,286 finance and ops staff found that 73% of Excel users get incorrect results from WEEKDAY() — not because they typed it wrong, but because they didn’t know Excel treats 'Sunday = 1' as the default, even in countries where Monday is the first workday.
The Problem
You’re building a weekly sales dashboard. Your raw data has dates in column A (A2:A11), and you need to label each row with its weekday — not for display, but to group Mon–Fri vs Sat–Sun for filtering. You type =WEEKDAY(A2) in B2 and drag down.
But your report shows Monday as 2, Sunday as 1 — and when you filter for "Weekday ≤ 5", you accidentally exclude Monday and include Sunday. Worse: your colleague in Berlin gets different results using the same formula.
| Date | Formula Used | Result | Expected Day | Actual Meaning |
|---|---|---|---|---|
| 2024-03-11 | =WEEKDAY(A2) | 2 | Monday | Monday (but only because Sunday=1) |
| 2024-03-15 | =WEEKDAY(A3) | 6 | Friday | Friday — correct, but fragile |
| 2024-03-17 | =WEEKDAY(A4) | 1 | Sunday | Sunday — breaks Mon–Fri filters |
| 2024-03-18 | =WEEKDAY(A5) | 2 | Monday | Same number as 2024-03-11 — no uniqueness |
| 2024-03-20 | =WEEKDAY(A6) | 4 | Wednesday | Correct — but only if you memorize the mapping |
| 2024-03-24 | =WEEKDAY(A7) | 1 | Sunday | Conflicts with Monday if you use =WEEKDAY(A2,2) |
The Solution
Do this — no exceptions:
- Type
=WEEKDAY(A2,2)in B2. The second argument is mandatory if you want predictable results. - Press Ctrl+Enter to keep focus in B2, then drag the fill handle down to B11.
- Select B2:B11 → press Alt+H, F, M to open Format Cells → choose Number tab → Category: Custom → Type:
ddd→ OK. Now you see "Mon", "Tue", etc., not numbers.
This forces Monday = 1, Tuesday = 2… Sunday = 7. No ambiguity. No country-specific surprises.
| Date | Formula | Result | Day Label | Workday? |
|---|---|---|---|---|
| 2024-03-11 | =WEEKDAY(A2,2) | 1 | Mon | Yes |
| 2024-03-15 | =WEEKDAY(A3,2) | 5 | Fri | Yes |
| 2024-03-17 | =WEEKDAY(A4,2) | 7 | Sun | No |
| 2024-03-18 | =WEEKDAY(A5,2) | 1 | Mon | Yes |
| 2024-03-20 | =WEEKDAY(A6,2) | 3 | Wed | Yes |
| 2024-03-24 | =WEEKDAY(A7,2) | 1 | Mon | Yes |
Going Further
You can embed WEEKDAY() inside other functions. For example:
- To flag weekend rows:
=IF(OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7),"Weekend","Weekday")in C2. - To sum sales only on Mondays:
=SUMIFS(C2:C11,A2:A11,">="&DATE(2024,3,11),A2:A11,"<="&DATE(2024,3,17),B2:B11,1)— where B2:B11 holdsWEEKDAY(A2:A11,2). - Use
=CHOOSE(WEEKDAY(A2,2),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")to skip custom formatting entirely. - For ISO week numbering (where Monday=1 and Week 1 is the one with the year’s first Thursday), combine with
ISOWEEKNUM()— but don’t confuse it withWEEKDAY(). They’re unrelated.
Surprising tip: If cell A2 contains text like "Mar 11, 2024" instead of a true date serial number, WEEKDAY(A2,2) returns #VALUE!. Fix it with =WEEKDAY(DATEVALUE(A2),2) — but only if A2 is consistent. Better: re-enter as real dates (Ctrl+1 → Date → pick format).
When NOT to Use This
Don’t use WEEKDAY() if:
- Your source data mixes date formats (e.g., some cells are text, some are numbers, some have time stamps). Clean first with
=IF(ISNUMBER(A2),A2,DATEVALUE(A2)). - You need to calculate business days between two dates — use
NETWORKDAYS(), notWEEKDAY(). - You’re working with fiscal weeks that start on Wednesday.
WEEKDAY()has no built-in support for arbitrary week starts — build your own logic withMOD()and offsets. - You’re comparing dates across time zones and daylight saving shifts. Serial numbers shift — test with known anchor dates like
DATE(2024,1,1).
Note: WEEKDAY(A2,11) exists (returns Mon=1 through Sun=7) but isn’t supported in Excel for Mac or older Excel versions. Stick with 2 unless you control every user’s environment.
Keyboard Shortcuts
| Action | Windows Shortcut | Mac Shortcut |
|---|---|---|
| Open Format Cells dialog | Ctrl+1 | Cmd+1 |
| Insert Function dialog | Shift+F3 | Fn+Shift+F3 |
| Edit formula in cell | F2 | Control+U |
| Fill down formula | Ctrl+D | Cmd+D |
