Calculating the week number in a spreadsheet
Project plans, shift rotas and reports are often built in a spreadsheet. The calendar week of a date can be determined there with a single formula — provided you choose the right function. The obvious function in many programs does not count according to ISO 8601, the standard used in Germany and most of Europe, by default.
For a quick lookup without a formula, use the calendar week calculator. How ISO counting works is explained in the guide Calendar weeks explained.
The right function: ISOWEEKNUM
Common spreadsheet programs with an English interface offer the function ISOWEEKNUM. If cell A1 contains a date,
=ISOWEEKNUM(A1)
returns the calendar week according to ISO 8601. Alternatively, =WEEKNUM(A1,21) works — type 21 stands for ISO counting. In programs with a German interface, the functions are called ISOKALENDERWOCHE(A1) and KALENDERWOCHE(A1;21).
The trap: WEEKNUM without a second argument
If you only write =WEEKNUM(A1), many programs use type 1: the week starts on Sunday, and the week containing 1 January is always week 1. That matches the counting used in the USA, not the ISO method.
An example shows the difference: for Friday, 1 January 2027, =ISOWEEKNUM(DATE(2027,1,1)) returns week 53 — correct, because the day belongs to week 53 of 2026. =WEEKNUM(DATE(2027,1,1)), on the other hand, returns 1. Throughout the year, the two methods repeatedly differ by one week.
Determining the week-numbering year
The week number alone is not enough around New Year, because week 53 of 1 January 2027 belongs to the year 2026. The matching week-numbering year is the year of the Thursday of the same week:
=YEAR(A1-WEEKDAY(A1,3)+3)
WEEKDAY with type 3 counts Monday as 0 through Sunday as 6. A1 minus this number gives the Monday of the week, plus three days the Thursday. A complete label such as "W53/2026" is produced by
="W"&ISOWEEKNUM(A1)&"/"&YEAR(A1-WEEKDAY(A1,3)+3)
Calculating the Monday of a given week
Often the reverse is needed: what is the date of the Monday of week 39 of 2026? If the year is in B1 and the week number in B2, the formula is:
=DATE(B1,1,4)-WEEKDAY(DATE(B1,1,4),3)+(B2-1)*7
4 January is always in week 1. The formula goes back from it to the Monday of that week and then counts forward the desired number of weeks. For 2026 and week 39 this gives Monday, 21 September 2026. Add 6 for the Sunday. Format the cell as a date, otherwise you will only see a serial number.
How many calendar weeks does a year have?
28 December always falls in the last calendar week of its year. That is why
=ISOWEEKNUM(DATE(B1,12,28))
returns the number of calendar weeks for any year: 53 for 2026, 52 for 2027. This lets you build week lists that automatically have the right length.
Practical tips
Watch the separator: programs with a German interface use a semicolon between arguments, English ones a comma. Always test formulas on a date around New Year, such as 1 January 2027 or 30 December 2024 (week 1 of 2025). And if you just need to look up a week quickly or want a ready-made year list as a CSV, the calendar week calculator does it without any formula.