Week Number Problem with Calendar

I've built a dynamic calendar in Excel that allows the user to set the start day and year they want to see. The only problem is the week numbers are not returning correctly.

As someone from Europe, I am familiar with the ISO 8601 calendar standard, which means:

• Week 1 begins on Monday.
• Week 1 must have at least four days belonging to the new year.

The ISOWEEKNUM function is ideal for this, however, it only works when the start day is Monday. WEEKNUM provides more options, but they are all based on the American system (except option 21), where the first week of the year is the one with January 1st in it and Sunday is the start day.

I want to apply the ISO 4/7 majority rule to other days as well. I found an customisable online calendar that does this perfectly, and I've dumped three screenshots in the example workbook.

Using Wednesday as the example, the week numbers that begin in G13 need to match the online calendar's. At the moment, I get strange results, e.g. 2022 starts at week 53 and then jumps to week 2.

Please see the attachment.

You'll see to the right of the calendar I've had a few attempts, but to no avail. I appreciate any help with this frustrating issue.

And you expect to see....

53 followed by 1,2,3, etc... or what??

In the example I've given, the year is 2022 and the start day is Wednesday. The week numbers in cell G13 need to match what's on the 2022 online calendar screenshot.

Instead of:

53
2
3
4
5
6
7
etc.

It should be:

1
2
3
4
5
6
7
etc.

Be aware of the differences between years. For example, 2022 starts at week 1, whereas 2023 begins at 53 and 2024 at 52.

The formula currently being used is:

Formula:

Please Login or Register  to view this content.

This doesn't work correctly.

Maybe try

=WEEKNUM(INDEX(AK13#,,1)-MOD(MATCH(dd1.StartDay,TEXT(SEQUENCE(7,,3),"dddd"),),7)+7*(TEXT(DATE(sp1.Year,1,1),"dddd")=dd1.StartDay),21)

If you want the weeknum start from 1, Then end of the year wouldn't reach Weekno. 53
But That is up to you.

I can't do inconsistence formula to make end of the previous year end at week 53, when the current year end at week 52.
I'm out.