Help with Excel: Weeknumber
Discussion
What I would like to do is put in a year and work out all the week start dates for week numbers in that year. I've played around and found a formula BUT it doesn't work for Week 1 of 2025 as Week 1 starts on the 30th Dec 2024. The rest of the weeks are fine. Any suggestions?
The formula is:
=MAX(DATE(A2,1,1),DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+(MID(A4,6,2)-1)*7+1)
(where A2 is the year number, and A4 contains the week number (week 1 for example)
Week 1 01/01/2025 <-- Wrong
Week 2 06/01/2025
Week 3 13/01/2025
Week 4 20/01/2025
Thanks.
The formula is:
=MAX(DATE(A2,1,1),DATE(A2,1,1)-WEEKDAY(DATE(A2,1,1),2)+(MID(A4,6,2)-1)*7+1)
(where A2 is the year number, and A4 contains the week number (week 1 for example)
Week 1 01/01/2025 <-- Wrong
Week 2 06/01/2025
Week 3 13/01/2025
Week 4 20/01/2025
Thanks.
Make sure you know which week numbering system you are using.
Excel Help said:
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.
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.
Surely the =WEEKNUM(CELLREF) formula will give you what you need
When I get a little stuck in Excel I tend to head to ablebits.
Their possible solutions for your problem can be found here:
https://www.ablebits.com/office-addins-blog/excel-...
When I get a little stuck in Excel I tend to head to ablebits.
Their possible solutions for your problem can be found here:
https://www.ablebits.com/office-addins-blog/excel-...
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff