Assign week numbers to dates based on multiple conditions in excel

Question

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

Week 1 starts on the first Sunday of the year.
Dates before the first Sunday should be assigned to the last week of the previous year.
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))

How do you assure 52 weeks a year if a week consists of 7 days so smaller than 365/366 days. Do you need the remaining days to be as week 52 (in other words week 52 will consist of more than 7 days)? — P.b
– P.b, Commented Sep 14, 2024 at 16:00
@mcloy, Also, what to do if last day of the year is on a Sunday (e.g., 12/31/2023). If we follow the rule of week 1 starting on the first Sunday of the year, then do we include 1/1/2024 through 1/6/2024 as belonging to week 52 of 2023, or would we start week 1 2024 on a Monday? Or something else? — bugdrown
– bugdrown, Commented Sep 15, 2024 at 19:44

nkalvi · Accepted Answer · 2024-09-14 14:22:07Z

1

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)
)

answered Sep 14, 2024 at 14:22

nkalvi

2,6142 gold badges12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign week numbers to dates based on multiple conditions in excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related