The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers
«
»

For older Excel versions that do not have the =ISOWEEKNUM(date) function, use =WEEKNUM(date,21)

Posted by jpluimers on 2024/02/14

Calculating an ISO-8601 based WEEKNUM

From [Wayback/Archive] ISOWEEKNUM Function – How to Get the Week Number in Excel:

  • If we are using an older version of MS Excel, we can use the function WEEKNUM. By default, the WEEKNUM function uses an arrangement where Week 1 begins on January 1, and Week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1).
    However, with MS Excel 2010 for Windows and MS Excel 2011 for Mac, we can generate an ISO week number using 21 as the return_type: =WEEKNUM(date,21).
  • There is no built-in worksheet function for ISO weeks before MS Excel 2010.

I tested that ISO-8601 week number calculation in with Excel 2011 on MacOS and Excel 2010 on Windows: the workaround works well for the dates mentioned in ISO week date – Wikipedia.

The reason this works is documented in [Wayback/Archive] WEEKNUM function:

Description

Returns the week number of a specific date. For example, the week containing January 1 is the first week of the year, and is numbered week 1.
There are two systems used for this function:
  • System 1    The week containing January 1 is the first week of the year, and is numbered week 1.
  • System 2    The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.

Syntax

WEEKNUM(serial_number,[return_type])
The WEEKNUM function syntax has the following arguments:
  • Serial_number     Required. A date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
  • Return_type     Optional. A number that determines on which day the week begins. The default is 1.
Return_type
Week begins on
System
1 or omitted
Sunday
1
2
Monday
1
11
Monday
1
12
Tuesday
1
13
Wednesday
1
14
Thursday
1
15
Friday
1
16
Saturday
1
17
Sunday
1
21
Monday
2

Calculating an ISO-8601 based WEEKYEAR using WEEKNUM

Since January 1st can fall in week 52 or 53 of the previous year, it is also important to be able to calculate an ISO-8601 based WEEKYEAR. Excel does not have a function for that, but you can use this:

=YEAR(date+X-WEEKNUM(date,21))

Now we need to get the value of X, and need to take into account that the outcome of WEEKNUM can be either of these:

  • 1
  • 53
  • 52

Various post have various values of X but do not indicate why they use a that value or use other formulas without explaining why they work or providing adequate test cases:

So I did some experimentation based on these quotes from:

  • ISO week date: First week – Wikipedia:

    The ISO 8601 definition for week 01 is the week with the first Thursday of the Gregorian year (i.e. of January) in it.

    … the earliest possible first week extends from Monday 29 December (previous Gregorian year) to Sunday 4 January, the latest possible first week extends from Monday 4 January to Sunday 10 January.

    If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in W01. If it is on a Friday, it is part of W53 of the previous year. If it is on a Saturday, it is part of the last week of the previous year which is numbered W52 in a common year and W53 in a leap year. If it is on a Sunday, it is part of W52 of the previous year.

  • ISO week date: Last week – Wikipedia

    … the earliest possible last week extends from Monday 22 December to Sunday 28 December, the latest possible last week extends from Monday 28 December to Sunday 3 January.

    If 31 December is on a Monday, Tuesday, or Wednesday it is in W01 of the next year. If it is on a Thursday, it is in W53 of the year just ending. If on a Friday it is in W52 of the year just ending in common years and W53 in leap years. If on a Saturday or Sunday, it is in W52 of the year just ending.

With a little help of the below links I made a small table of example years and dates for the earliest through latest possible first and last weeks of the year and the ####W ISO-8601 week-year number of various dates.

In this table, bold values indicate where YEAR differs from WEEKYEAR.

3 4 50 51 3 4 50 51
First weekday Offset Offset Offset Offset Last weekday Offset Offset Offset Offset
DATE WEEKDAY YEAR WEEKYEAR DATE DATE DATE DATE DATE WEEKDAY YEAR WEEKYEAR DATE DATE DATE DATE
boundary check 2020-12-21 Monday 2020 2020 2020-11-02 2020-11-03 2020-12-19 2020-12-20 boundary check 2020-12-27 Sunday 2020 2020 2020-11-08 2020-11-09 2020-12-25 2020-12-26
earliest last week 2025-12-22 Monday 2025 2025 2025-11-03 2025-11-04 2025-12-20 2025-12-21 earliest last week 2025-12-28 Sunday 2025 2025 2025-11-09 2025-11-10 2025-12-26 2025-12-27
2024-12-23 Monday 2024 2024 2024-11-04 2024-11-05 2024-12-21 2024-12-22 2024-12-29 Sunday 2024 2024 2024-11-10 2024-11-11 2024-12-27 2024-12-28
2029-12-24 Monday 2029 2029 2029-11-05 2029-11-06 2029-12-22 2029-12-23 2029-12-30 Sunday 2029 2029 2029-11-11 2029-11-12 2029-12-28 2029-12-29
2023-12-25 Monday 2023 2023 2023-11-06 2023-11-07 2023-12-23 2023-12-24 2023-12-31 Sunday 2023 2023 2023-11-12 2023-11-13 2023-12-29 2023-12-30
2022-12-26 Monday 2022 2022 2022-11-07 2022-11-08 2022-12-24 2022-12-25 2023-01-01 Sunday 2023 2022 2022-11-13 2022-11-14 2022-12-30 2022-12-31
2021-12-27 Monday 2021 2021 2021-11-08 2021-11-09 2021-12-25 2021-12-26 2022-01-02 Sunday 2022 2021 2021-11-14 2021-11-15 2021-12-31 2022-01-01
latest last week 2020-12-28 Monday 2020 2020 2020-11-08 2020-11-09 2020-12-25 2020-12-26 latest last week 2021-01-03 Sunday 2021 2020 2020-11-14 2020-11-15 2020-12-31 2021-01-01
earliest first week 2025-12-29 Monday 2025 2026 2025-12-31 2026-01-01 2026-02-16 2026-02-17 earliest first week 2026-01-04 Sunday 2026 2026 2026-01-06 2026-01-07 2026-02-22 2026-02-23
2024-12-30 Monday 2024 2025 2025-01-01 2025-01-02 2025-02-17 2025-02-18 2025-01-05 Sunday 2025 2025 2025-01-07 2025-01-08 2025-02-23 2025-02-24
2029-12-31 Monday 2029 2030 2030-01-02 2030-01-03 2030-02-18 2030-02-19 2030-01-06 Sunday 2030 2030 2030-01-08 2030-01-09 2030-02-24 2030-02-25
2024-01-01 Monday 2024 2024 2024-01-03 2024-01-04 2024-02-19 2024-02-20 2024-01-07 Sunday 2024 2024 2024-01-09 2024-01-10 2024-02-25 2024-02-26
2023-01-02 Monday 2023 2023 2023-01-04 2023-01-05 2023-02-20 2023-02-21 2023-01-08 Sunday 2023 2023 2023-01-10 2023-01-11 2023-02-26 2023-02-27
2022-01-03 Monday 2022 2022 2022-01-05 2022-01-06 2022-02-21 2022-02-22 2022-01-09 Sunday 2022 2022 2022-01-11 2022-01-12 2022-02-27 2022-02-28
latest first week 2021-01-04 Monday 2021 2021 2021-01-06 2021-01-07 2021-02-22 2021-02-23 latest first week 2021-01-10 Sunday 2021 2021 2021-01-12 2021-01-13 2021-02-28 2021-03-01
boundary check 2026-01-05 Monday 2026 2026 2026-01-06 2026-01-07 2026-02-22 2026-02-23 boundary check 2026-01-11 Sunday 2026 2026 2026-01-12 2026-01-13 2026-02-28 2026-03-01

 

Queries used

–jeroen

This entry was posted on 2024/02/14 at 12:00 and is filed under Excel, Office, Office 2010, Office 2011 for Mac, Power User. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«
»