excel-formulaweek-numberweekday

Assign week numbers to dates based on multiple conditions in excel

I'm trying to assign week numbers to dates between 2022 and 2024 such that it satisfies the requirements below:

  1. Week 1 starts on the first Sunday of the year.
  2. Dates before the first Sunday should be assigned to the last week of the previous year.
  3. Ensure there are only 52 weeks per year

For example, 1st Jan 2022 falls on Saturday, so it should be assigned 52 (week 52 of year 2021). 2nd Jan 2022 falls on Sunday, hence it should be assigned 1 (week 1 of year 2022). Similarly, 31st Dec 2022 falls on Saturday, so it should be 52 and 1st Jan 2023 falls on Sunday, so it should be 1.

I tried the code below and it assigns week 52 for 1st Jan 2022, week 1 for 2nd Jan 2022 but week 53 for 1st jan 2023 though it should be week 1 (Assume the date column is in cell A. The format of my date is yyyy-mm-dd).

if(a1<date(year(a1),1,1)+(8-weekday(date(year(a1),1,1),1)), weeknum(date(year(a1),1,1)-weekday(date(year(a1),1,1),1)+1,1),weeknum(a1-weekday(a1,1),1))

Solution

  • Avoiding recursion, here's a (perhaps crude) way:

    Dynamic formula, Excel 2021 and later

    =LET(
    start_date, "2022-1-1",
    end_date, "2025-1-1",
    num_days, DAYS(end_date, start_date),
    dates, SEQUENCE(num_days, , start_date),
    years_starting_on_sunday, {1978, 1989, 1995, 2006, 2017, 2023, 2034},
    sunny_weeknum, IFS(
        ISNUMBER(XMATCH(YEAR(dates), years_starting_on_sunday)),
        WEEKNUM(dates),
        WEEKNUM(dates) = 1,
        IF(
            ISNUMBER(XMATCH(YEAR(dates) - 1, years_starting_on_sunday)),
            53,
            52
        ),
        TRUE,
        WEEKNUM(dates) - 1
    ),
    HSTACK(dates, sunny_weeknum)
)

    Result