List of ISO week numbers between 2 dates

[willow1985]

Hello,

I need help with a formula that will give me a list of the ISO week numbers between 2 dates.

Customer Part Analysis Dashboard.xlsm
	A	B	C	D
1	Todays date	2021-03-18		Week Number List
2	Year Selected	2021		53
3	Input Date Range	44197	44227	1
4				2
5				3
6				4
Graph Data

Cell Formulas
Range	Formula
B1	B1	=TODAY()

Thank you to anyone who can help!

Note: The 2 dates are linked cells from a combo box so they come through in that format - as a value, but my other formulas compensate for the format so I need the Week number formula to be able to do the same.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Todays date	18/03/2021		Week Number List
2	Year Selected	2021		53
3	Input Date Range	01/01/2021	31/01/2021	1
4				2
5				3
6				4
7
8
Master

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D8	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))

 

[willow1985]

How about

+Fluff 1.xlsm
	A	B	C	D
1	Todays date	18/03/2021		Week Number List
2	Year Selected	2021		53
3	Input Date Range	01/01/2021	31/01/2021	1
4				2
5				3
6				4
7
8
Master

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D8	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))

Is there any way to do a spill formula? (I think that's what it is called). One that will stop filling down the column when it reaches the end date? (week 4)

Customer Part Analysis Dashboard.xlsm
	A	B	C	D
1	Today's date	2021-03-18		Week Number List
2	Year Selected	2021		53
3	Input Date Range	44197	44227	1
4				2
5				3
6				4
7				5
8				6
9				7
10				8
11				9
12				10
13				11
Graph Data

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))

 

[Fluff]

With 2019 formulae won't spill, but that formula should return blank cells after week 4 & does for me.

+Fluff 1.xlsm
	A	B	C	D
1	Today's date	18/03/2021		Week Number List
2	Year Selected	2021		53
3	Input Date Range	44197	44227	1
4				2
5				3
6				4
7
8
9
10
11
12
13
Master

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))

 

[Fluff]

What do you get if you put this formula in E2 & drag down

+Fluff 1.xlsm
	A	B	C	D	E
1	Today's date	18/03/2021		Week Number List
2	Year Selected	2021		53	44197
3	Input Date Range	44197	44227	1	44204
4				2	44211
5				3	44218
6				4	44225
7					44232
8					44239
9					44246
10					44253
11					44260
12					44267
13					44274
Master

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13	E2	=$B$3+ROWS(D$2:D2)*7-7

 

[willow1985]

What do you get if you put this formula in E2 & drag down

+Fluff 1.xlsm
	A	B	C	D	E
1	Today's date	18/03/2021		Week Number List
2	Year Selected	2021		53	44197
3	Input Date Range	44197	44227	1	44204
4				2	44211
5				3	44218
6				4	44225
7					44232
8					44239
9					44246
10					44253
11					44260
12					44267
13					44274
Master

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13	E2	=$B$3+ROWS(D$2:D2)*7-7

Customer Part Analysis Dashboard.xlsm
	A	B	C	D	E
1	Today's date	2021-03-18		Week Number List
2	Year Selected	2021		53	January 1, 2021
3	Input Date Range	44197	44227	1	January 8, 2021
4				2	January 15, 2021
5				3	January 22, 2021
6				4	January 29, 2021
7				5	February 5, 2021
8				6	February 12, 2021
9				7	February 19, 2021
10				8	February 26, 2021
11				9	March 5, 2021
12				10	March 12, 2021
13				11	March 19, 2021
Graph Data

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13	E2	=$B$3+ROWS(D$2:D2)*7-7

And I apologize, I now have office 365. Updated this week. But many users who will be using this spreadsheet may still have 2019

 

[willow1985]

Got it!
Formula: =IF(VALUE($B$3)+ROWS(D$2:D2)*7-7>VALUE($C$3),"",ISOWEEKNUM(VALUE($B$3)+ROWS(D$2:D2)*7-7))

Customer Part Analysis Dashboard.xlsm
	A	B	C	D	E
1	Today's date	2021-03-18		Week Number List
2	Year Selected	2021		53	January 1, 2021
3	Input Date Range	44197	44227	1	January 8, 2021
4				2	January 15, 2021
5				3	January 22, 2021
6				4	January 29, 2021
7					February 5, 2021
8					February 12, 2021
9					February 19, 2021
10					February 26, 2021
11					March 5, 2021
12					March 12, 2021
13					March 19, 2021
Graph Data

Cell Formulas
Range	Formula
B1	B1	=TODAY()
D2:D13	D2	=IF(VALUE($B$3)+ROWS(D$2:D2)*7-7>VALUE($C$3),"",ISOWEEKNUM(VALUE($B$3)+ROWS(D$2:D2)*7-7))
E2:E13	E2	=$B$3+ROWS(D$2:D2)*7-7

 

[Fluff]

That suggests that B3 & C3 are text rather than numbers, is that the case?
As you now have 365 you could use

=ISOWEEKNUM(B3+SEQUENCE(ROUNDUP((C3-B3)/7,0),,0,7))

 

[willow1985]

That suggests that B3 & C3 are text rather than numbers, is that the case?
As you now have 365 you could use

=ISOWEEKNUM(B3+SEQUENCE(ROUNDUP((C3-B3)/7,0),,0,7))

Ill keep your original just because I still have users on 2019, but thank you very much!
Yes I have those cells linked to a combo box which I have a code on to show text instead of numbers.

Thank you again!

 

[Fluff]

You're welcome & thanks for the feedback.