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.
Bookmarks