What it does
Without a return_type, the week starts on Sunday and the week containing 1 January is week 1. For weeks that start on Monday, use =WEEKNUM(A2,2): Sunday stays in the same week and the new week begins on Monday.
Type 21 returns the ISO 8601 week number: the week starts on Monday and week 1 is the week that contains the first Thursday of the year. As a result, the first days of a year can belong to the last week (52 or 53) of the previous one; Friday 1 January 2027 is ISO week 53 of 2026. Plans shared with suppliers usually use ISO numbers; ISOWEEKNUM gives the same result.
To total by week, the simplest way is to add a week column and sum it with SUMIF. For the day of the week, use WEEKDAY.
Syntax
=WEEKNUM(serial_number, [return_type])=HAFTASAY(tarih; [tür])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
serial_number | Required | The date, or the cell holding the date, whose week you want. |
return_type | Optional | The day the week starts on and the numbering system. 1 or omitted: Sunday. 2: Monday. 11-17: a chosen start day from Monday to Sunday. 21: ISO 8601. |
Examples
1The week each of three types gives for the same dates
The same dates are numbered with type 1 (Sunday start), type 2 (Monday start) and type 21 (ISO). The differences show up on Sundays and at the start of the year.
=WEEKNUM(A2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Day | Type 1 | Type 2 | Type 21 (ISO) |
| 2 | 7-Nov-2026 | Saturday | 45 | 45 | 45 |
| 3 | 8-Nov-2026 | Sunday | 46 | 45 | 45 |
| 4 | 9-Nov-2026 | Monday | 46 | 46 | 46 |
| 5 | 1-Jan-2027 | Friday | 1 | 1 | 53 |
| 6 | 4-Jan-2027 | Monday | 2 | 2 | 1 |
- C2 (filled down to C6)
=WEEKNUM(A2) - D2 (filled down to D6)
=WEEKNUM(A2,2) - E2 (filled down to E6)
=WEEKNUM(A2,21)
Sunday 8 November moves to week 46 with type 1 but stays in week 45 with type 2. For 1 January 2027, type 2 gives 1 while the ISO number is 53; under ISO, the first week of 2027 starts on 4 January: 1.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Weekly production totals
Column C finds each record's week with type 2; SUMIF in column F adds the output of each week. The Sunday record stays inside the week that began on Monday.
=WEEKNUM(A2,2)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Date | Output (Units) | Week | Week | Total | |
| 2 | 2-Nov-2026 | 420 | 45 | 45 | 1,365 | |
| 3 | 4-Nov-2026 | 385 | 45 | 46 | 835 | |
| 4 | 6-Nov-2026 | 410 | 45 | |||
| 5 | 8-Nov-2026 | 150 | 45 | |||
| 6 | 10-Nov-2026 | 395 | 46 | |||
| 7 | 12-Nov-2026 | 440 | 46 |
- C2 (filled down to C7)
=WEEKNUM(A2,2) - F2 (filled down to F3)
=SUMIF($C$2:$C$7,E2,$B$2:$B$7)
The Sunday 8 November record is in week 45, so it counts towards that week's total: 1,365. The following week totals 835.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Different week number
Cause: For the same date, a supplier's plan and your table show different weeks; one uses type 2, the other ISO (type 21).
Fix: Agree on one type; the two systems drift apart especially at the start of the year.
- #NUM!
Cause: return_type is not valid, e.g. 3.
Fix: Use 1, 2, 11-17 or 21.
Good to know
- With ISO weeks, days at the start of a year can belong to the previous year; if you need the ISO year with the week number, use
=YEAR(A2-WEEKDAY(A2,2)+4). - To turn the week number into a sortable code with the year:
=YEAR(A2)*100+WEEKNUM(A2,2).
In Nexell
Get Nexell to do it from a sentence
Add a Week column to the production records based on Monday-first weeks, and give the total output for each week in a separate table
Convert to tableBring your production list into the tool and type this. Nexell builds the week column and the weekly summary with real Excel formulas and shows them in the preview; in the downloaded file, the summary updates when a figure changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I get the ISO week number in Excel?
Use =WEEKNUM(A2,21) or =ISOWEEKNUM(A2). Both count the week that starts on Monday and contains the first Thursday of the year as week 1.