Help with Excel: Weeknumber

Author
Discussion

boxst

Original Poster:

3,801 posts

152 months

Wednesday 3rd July
quotequote all
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.

MustangGT

12,253 posts

287 months

Wednesday 3rd July
quotequote all
A simple solution would be to put in 30th December manually for week 1 formatted as date, then simply add 7 to the formula in each cell going down. Does not need to be a lookup or calculation.

boxst

Original Poster:

3,801 posts

152 months

Wednesday 3rd July
quotequote all
Thank you for the suggestion, I wanted it to be automatic so I can just put a year in and the weeks are autopopulated.

jeremyc

24,528 posts

291 months

Wednesday 3rd July
quotequote all
Make sure you know which week numbering system you are using. nerd

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.

RustyMX5

8,250 posts

224 months

Wednesday 3rd July
quotequote all
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-...

boxst

Original Poster:

3,801 posts

152 months

Wednesday 3rd July
quotequote all
Thank you for your help.

This:

=DATE($A$2,1,-2)-WEEKDAY(DATE($A$2,1,3))+((MID(A5,6,2)*7)-1)

worked in the end.