Part of the free Module 5: Excel Formulas and Functions · Function 99 of 105 · Full Excel course
The Excel WEEKNUM function returns the week number of the year for a date, from 1 to 53. By default the week containing 1 January is week 1 and weeks start on Sunday, but the optional return_type argument lets weeks start on any day or switch to the ISO 8601 standard. WEEKNUM is used to group sales, tickets and timesheets into weekly periods for reports and charts.
Syntax
=WEEKNUM(serial_number, [return_type])Arguments
| Argument | Required / Optional | Meaning |
|---|---|---|
| serial_number | Required | The date to evaluate: a date cell, TODAY(), a DATE result or recognisable date text. |
| return_type | Optional | 1 or omitted: weeks start Sunday. 2: weeks start Monday. 11 to 17: weeks start Monday through Sunday respectively. 21: ISO week number (weeks start Monday, week 1 contains the first Thursday of the year). |
How WEEKNUM works
With return types 1 to 17, Excel uses “System 1”: the week containing 1 January is week 1, however many days it has, and each new week begins on the chosen start day. That means the first and last weeks can be short, and a year can have 53 (very occasionally 54) weeks. With return type 21, Excel uses “System 2”, the ISO standard: weeks start on Monday and week 1 is the week that contains the first Thursday, so the first days of January can belong to week 52 or 53 of the previous year. =WEEKNUM("2026-09-04") returns 36; =WEEKNUM("2026-01-01",21) returns 1 because 1 January 2026 is a Thursday. Invalid dates return #VALUE! and unsupported return types #NUM!.
Step-by-step example: weekly sales summary
- Column A holds transaction dates and column B the amounts.
- In C2 enter
=WEEKNUM(A2,2)so that weeks run Monday to Sunday, and fill down. - In E2:E54 list week numbers 1 to 53 and in F2 enter
=SUMIF($C$2:$C$1000,E2,$B$2:$B$1000). - Fill F2 down and chart columns E and F. If the data spans several years, add
=YEAR(A2)&"-W"&TEXT(WEEKNUM(A2,2),"00")in column D and summarise by that key instead.
Practical use cases
1. ISO week for international reporting
=WEEKNUM(A2,21)Identical to ISOWEEKNUM(A2), which is available in Excel 2013 and later.
2. Year-week key that sorts correctly
=YEAR(A2)*100+WEEKNUM(A2,2)3. Current week flag
=WEEKNUM(A2,2)=WEEKNUM(TODAY(),2)4. Start date of the week from a week number
=DATE(B2,1,1)+(C2-1)*7-WEEKDAY(DATE(B2,1,1),2)+1With the year in B2 and the week number in C2, this returns the Monday of that week under System 1 with Monday start.
5. Week of the month
=WEEKNUM(A2,2)-WEEKNUM(EOMONTH(A2,-1)+1,2)+1Common mistakes and tips
- Mixing systems: WEEKNUM(A2) and WEEKNUM(A2,21) can differ by one for the same date. Pick one convention for the whole workbook and document it.
- Year boundaries: under the ISO system, 1 January can be week 52 or 53 of the previous year. Pair the week with the ISO year (YEAR of the Thursday in that week) rather than the calendar year.
- Result shown as a date: the cell is date-formatted; switch to General.
- Text dates: regional formats can misread “04/09/2026”; use real date cells.
- Google Sheets: WEEKNUM supports the same return types, including 21, so formulas transfer directly.
WEEKNUM compared with ISOWEEKNUM, WEEKDAY and PivotTable grouping
ISOWEEKNUM is a dedicated function that always returns the ISO week and equals WEEKNUM with return type 21; use it when the workbook must open in Excel 2013 or later and ISO weeks are required, and use WEEKNUM when you need Sunday-start or custom-start weeks. WEEKDAY tells you the day within the week, not the week within the year, and the two are often combined to derive week start and end dates. PivotTables can group dates by days with a 7-day interval, which produces week buckets without a helper column, but the buckets start from the first date in the data rather than a calendar rule. For dashboards that must match finance or operations calendars, an explicit WEEKNUM column with a documented return type is the more reliable approach.
Related functions
- WEEKDAY: returns the day of the week.
- YEAR: pairs with WEEKNUM to build year-week keys.
- DATE: rebuilds a date from a year and week number.
- EOMONTH: month boundaries for week-of-month calculations.
- See all lessons in the Excel Formulas course.
Frequently asked questions
How do I get the ISO week number in Excel?
Use =WEEKNUM(A2,21) or =ISOWEEKNUM(A2). Both start weeks on Monday and treat the week containing the first Thursday as week 1.
Why does WEEKNUM give a different result from my calendar?
Excel’s default system makes the week containing 1 January week 1 and starts weeks on Sunday. Most European calendars use ISO weeks, so switch to return type 21.
Can WEEKNUM return 53?
Yes. Years in which 1 January falls late in the week have 53 weeks under the default system, and ISO years with a Thursday 1 January (or a leap-year Wednesday) also have 53.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.