Dates function
Excel WEEKNUM function
WEEKNUM returns the week of the year a date falls in, from 1 to 54. Week 1 is always the week that contains January 1, so it can be very short. The second argument sets the day weeks start on; use 21 for ISO weeks, which many European and payroll systems use.
=WEEKNUM(serial_number, [return_type])Arguments
serial_number- The date.
return_type- Optional. 1 or 17 weeks start Sunday (default), 2 or 11 Monday, 12 Tuesday, 13 Wednesday, 14 Thursday, 15 Friday, 16 Saturday, 21 ISO weeks starting Monday.
WEEKNUM examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Date | Amount |
| 2 | SO-901 | Dec 28, 2026 | 340 |
| 3 | SO-902 | Dec 30, 2026 | 125 |
| 4 | SO-903 | Dec 31, 2026 | 610 |
| 5 | SO-904 | Jan 1, 2027 | 95 |
| 6 | SO-905 | Jan 3, 2027 | 220 |
| 7 | SO-906 | Jan 4, 2027 | 480 |
| 8 | SO-907 | Jan 6, 2027 | 150 |
Download this sheet as an Excel practice file (.xlsx)
Result: 53
December 31, 2026 is in week 53 of 2026.
Result: 1
The next day, January 1, 2027, starts week 1 of 2027. It is a Friday, so with weeks starting on Sunday week 1 lasts just two days.
Result: 2 / 1
Sunday January 3 is in week 2 when weeks start on Sunday, but still week 1 when they start on Monday (return_type 2).
Result: 53
Return type 21 gives the ISO week: January 1, 2027 belongs to ISO week 53 of 2026, because ISO week 1 is the week with the year’s first Thursday.
How to use WEEKNUM
- Click the cell next to the date.
- Type
=WEEKNUM(B2)for weeks starting Sunday, or=WEEKNUM(B2,2)for Monday. - Use
=WEEKNUM(B2,21)or ISOWEEKNUM for ISO week numbers. - Fill down and group or total by the week column.
Common WEEKNUM mistakes
Grouping by week number across years
Week 1 of 2027 and week 1 of 2026 have the same number. Group by year and week together, such as YEAR(B2)&"-"&WEEKNUM(B2).
Mixing return types
Sunday-start and Monday-start week numbers differ for every Sunday. Use one return type for the whole workbook.
Expecting ISO weeks by default
The default counts from January 1 with Sunday starts. For ISO 8601 weeks, use 21 or ISOWEEKNUM, and pair it with the ISO year.
WEEKNUM FAQ
How do I get the week number from a date in Excel?
Use =WEEKNUM(date) for weeks starting Sunday, =WEEKNUM(date,2) for Monday, or =ISOWEEKNUM(date) for ISO weeks.
Why does WEEKNUM return 53?
With WEEKNUM, the last days of December often fall in week 53 because week 1 starts on January 1. A leap year that starts on a Saturday can even reach week 54.
How do I sum values by week number?
Use =SUMPRODUCT((WEEKNUM(B2:B8,2)=E2)*C2:C8), or add a helper column with WEEKNUM and use SUMIF on it.
Guides that use WEEKNUM
Related functions
Updated