I came accross a nice “feature” in excel… the WEEK() function, when applied to 2021-01-04 returns the value “2” (week #2 of 2021). BUT, when applied to 2020-12-29 (monday 7 days before), it will return “53”. So… where’s the week #1 gone ?

Outlook is reporting the week beginning with 2021-01-04 as being week #1 of 2021, same applies to my wall calendar. A bug in Excel ?

Answer is not so easy… let’s have a look at Wikipedia’s ISO8601 date format

There are several mutually equivalent and compatible descriptions of week 01:

• the week with the year’s first Thursday in it (the formal ISO definition),
• the week with 4 January in it,
• the first week with the majority (four or more) of its days in the starting year, and
• the week starting with the Monday in the period 29 December – 4 January.

In Outlook, the options->calendar dialog offers a choice between three out of the four variants. Furthermore, Excel provides a function ISOWEEK() that implements the ISO definition of week 01.

LibreOffice conversely provides the WEEKNUM() and WEEKNUM_EXCEL2003() functions. Both return “2” - only the ISOWEEKNUM() function returns the correct value “1”.

Summary : take particular attention at the functions you’re using in your (MS|Libre) office formulas, it may lead to some strange side effects in 2021 !