Excel guide
Week numbers in Excel
Use ISOWEEKNUM for ISO weeks, combine it with the ISO year, and avoid common New Year errors.
Return the ISO week number
If cell A2 contains a real Excel date:
=ISOWEEKNUM(A2)This returns 1 through 53 according to ISO 8601. It does not return the ISO year.
Build a complete ISO code
The Thursday of the date's week always falls in the correct ISO year:
=YEAR(A2-WEEKDAY(A2,2)+4)&"-W"&TEXT(ISOWEEKNUM(A2),"00")For January 1, 2021, the result is 2020-W53.
ISOWEEKNUM versus WEEKNUM
ISOWEEKNUM(date)
Use this when you need ISO 8601: Monday-first weeks and the first-Thursday rule.
WEEKNUM(date, return_type)
This supports several week-start conventions. WEEKNUM(A2,21) uses the ISO system in current Excel versions, but ISOWEEKNUM(A2) states the intent more clearly.
WEEKNUM return types
The second argument of WEEKNUM sets the first day of the week. Only type 21 follows ISO 8601; all other types count the week containing January 1 as week 1.
| return_type | Week starts on | Week 1 is |
|---|---|---|
1 or omitted | Sunday | Week containing January 1 |
2 | Monday | Week containing January 1 |
11 | Monday | Week containing January 1 |
12 | Tuesday | Week containing January 1 |
13 | Wednesday | Week containing January 1 |
14 | Thursday | Week containing January 1 |
15 | Friday | Week containing January 1 |
16 | Saturday | Week containing January 1 |
17 | Sunday | Week containing January 1 |
21 | Monday | Week containing the first Thursday (ISO 8601) |
Example for Thursday, January 1, 2026: =WEEKNUM(A2) returns 1 and =ISOWEEKNUM(A2) also returns 1. For Friday, January 1, 2027, WEEKNUM returns 1 but ISOWEEKNUM returns 53, because that day belongs to ISO week 53 of 2026.
Convert a week number back to a date
With the ISO year in B2 and the week in C2, this returns the Monday of that ISO week:
=DATE(B2,1,4)-WEEKDAY(DATE(B2,1,4),2)+1+(C2-1)*7Add 6 for the Sunday. Format the cell as a date.
Common errors
#VALUE!: the cell contains text that looks like a date. UseDATEVALUEor Data → Text to Columns to convert it.#NUM!: the return type is not one of the values in the table above.- Day and month swapped: 03/04/2026 means March 4 in US settings and April 3 in most other regions. Check the regional format, or enter dates as 2026-04-03.
- Old Excel versions:
ISOWEEKNUMneeds Excel 2013 or later, and return type 21 needs Excel 2010 or later. - Using
YEAR(A2)for the ISO year: wrong near some year boundaries. - Dates stored as text: convert them to real date values before calculating.
- Leaving out the return type in WEEKNUM: the default may not match ISO.
- Not padding the week: use
TEXT(...,"00")for codes such as W03.
Reference: Microsoft's ISOWEEKNUM documentation.
Week numbers in Google Sheets
Google Sheets uses the same functions and return types as Excel:
=ISOWEEKNUM(A2)=WEEKNUM(A2,21)The ISO-code formula above also works unchanged in Sheets. To number a whole column at once, use =ARRAYFORMULA(IF(A2:A="",,ISOWEEKNUM(A2:A))). Sheets treats dates typed in the spreadsheet's locale as real dates; imported CSV dates may need DATEVALUE first.