WEEKNUM and ISOWEEKNUM in Excel and Google Sheets

Two functions with different rules

Excel and Google Sheets both have two week functions. ISOWEEKNUM returns the ISO 8601 week number: weeks start on Monday, and week 1 is the week with the first Thursday. WEEKNUM returns a simpler number: by default, weeks start on Sunday, and week 1 is the week that contains 1 January. If you type the same date into both, they often agree in the middle of the year and differ around New Year and on Sundays.

For 22 October 2026, Excel stores the date as the serial number 46317, and both functions return 43. For Friday 1 January 2027, ISOWEEKNUM returns 53, because the date belongs to the last week of 2026, while WEEKNUM returns 1. For Sunday 25 October 2026, ISOWEEKNUM returns 43 and WEEKNUM returns 44, because the default WEEKNUM starts a new week on Sunday.

The return types of WEEKNUM

WEEKNUM takes an optional second argument that selects the first day of the week. The value 1, which is also the default, starts the week on Sunday. The value 2 starts on Monday. The values 11 to 17 start the week on Monday, Tuesday, Wednesday, Thursday, Friday, Saturday and Sunday in that order. In all these types, the week that contains 1 January is week 1.

The value 21 is different: it returns the ISO number, and it exists so that WEEKNUM can do what ISOWEEKNUM does. The formula =WEEKNUM(A1,21) is identical to =ISOWEEKNUM(A1). Older versions of Excel before 2013 have no ISOWEEKNUM, so WEEKNUM with 21 is the portable choice for files that are shared with people on old versions.

Week 54 and other surprises

Because the plain WEEKNUM counts a partial first week as week 1, a year can end with a week 54. In 2028, a leap year that starts on a Saturday, WEEKNUM of 31 December 2028 returns 54. The ISO number of the same date is 52. A table that expects numbers from 1 to 53 will break.

The second surprise is that WEEKNUM changes on 1 January, not on the first day of a week. The days from Sunday 28 December 2025 to Wednesday 31 December 2025 return 53, and Thursday 1 January 2026 returns 1, although they are all in the same Sunday-to-Saturday week. If you plot weekly data with WEEKNUM, the week of New Year is cut in two.

The week-year formula

ISOWEEKNUM returns only the number. To label the week correctly you need the week-year as well, which can differ from the year of the date near New Year. The year of the Thursday of the week is the week-year, and you can get it in one formula: =YEAR(A1-WEEKDAY(A1,2)+4). WEEKDAY with 2 returns 1 for Monday to 7 for Sunday, so subtracting it moves to the day before the Monday and adding 4 reaches the Thursday.

A full ISO label, such as 2026-W43, is =YEAR(A1-WEEKDAY(A1,2)+4)&"-W"&TEXT(ISOWEEKNUM(A1),"00"). With it, every date in the same ISO week gets the same label, and the labels sort in the right order, including across New Year. Use this column for pivot tables and charts instead of the year of the date.

From week number to dates

To go the other way, from a year and a week number to the Monday of that week, use =DATE(y,1,4)-WEEKDAY(DATE(y,1,4),2)+1+7*(n-1). The first part finds the Monday of week 1 as the Monday on or before 4 January, and the last part adds whole weeks. For y = 2026 and n = 47 it returns Monday 16 November 2026. Add 6 for the Sunday.

To count the weeks of a year, use =ISOWEEKNUM(DATE(y,12,28)). 28 December is always in the last ISO week of its year, so the result is 52 or 53. For 2026 it returns 53, and for 2027 it returns 52. This is a safe way to validate week numbers in a form: reject any week larger than the result.

Which function should you use?

Use ISOWEEKNUM, or WEEKNUM with 21, for anything that is shared across countries, uses Monday as the first day, or has to match calendars, supply chains and software outside the United States. It gives each week to exactly one year and never produces week 54.

Use plain WEEKNUM only if you must reproduce an existing report that uses the US rule. Write the rule in a note on the sheet, and keep the argument explicit, for example WEEKNUM(A1,1), so that the next reader knows it was chosen on purpose.

Whichever function you use, check the edges of the year. Put 29 December, 31 December, 1 January and 4 January into a test row and compare the results with the calculator on this page. If both agree on these dates, your formulas are consistent.

Differences between Excel and Google Sheets

Excel and Google Sheets agree on the behaviour of both functions. The difference is in the details around them. Excel for Windows and Mac stores dates as serial numbers starting from 1900, while Google Sheets counts from 30 December 1899. For modern dates the two give the same numbers, and the week functions are not affected.

Localised versions of the programs translate the function names. A German Excel calls ISOWEEKNUM ISOKALENDERWOCHE and WEEKNUM KALENDERWOCHE. If a file moves between language versions, the formulas are translated automatically, but copying formulas as text between versions will fail.