List of ISO week numbers between 2 dates

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
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
ABCD
1Todays date2021-03-18Week Number List
2Year Selected202153
3Input Date Range44197442271
42
53
64
Graph Data
Cell Formulas
RangeFormula
B1B1=TODAY()


1616075913632.png


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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCD
1Todays date18/03/2021Week Number List
2Year Selected202153
3Input Date Range01/01/202131/01/20211
42
53
64
7 
8 
Master
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D8D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
 
Solution

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
ABCD
1Todays date18/03/2021Week Number List
2Year Selected202153
3Input Date Range01/01/202131/01/20211
42
53
64
7 
8 
Master
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D8D2=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
ABCD
1Today's date2021-03-18Week Number List
2Year Selected202153
3Input Date Range44197442271
42
53
64
75
86
97
108
119
1210
1311
Graph Data
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
With 2019 formulae won't spill, but that formula should return blank cells after week 4 & does for me.
+Fluff 1.xlsm
ABCD
1Today's date18/03/2021Week Number List
2Year Selected202153
3Input Date Range44197442271
42
53
64
7 
8 
9 
10 
11 
12 
13 
Master
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What do you get if you put this formula in E2 & drag down
+Fluff 1.xlsm
ABCDE
1Today's date18/03/2021Week Number List
2Year Selected20215344197
3Input Date Range4419744227144204
4244211
5344218
6444225
7 44232
8 44239
9 44246
10 44253
11 44260
12 44267
13 44274
Master
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13E2=$B$3+ROWS(D$2:D2)*7-7
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
What do you get if you put this formula in E2 & drag down
+Fluff 1.xlsm
ABCDE
1Today's date18/03/2021Week Number List
2Year Selected20215344197
3Input Date Range4419744227144204
4244211
5344218
6444225
7 44232
8 44239
9 44246
10 44253
11 44260
12 44267
13 44274
Master
Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13E2=$B$3+ROWS(D$2:D2)*7-7

Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF($B$3+ROWS(D$2:D2)*7-7>$C$3,"",ISOWEEKNUM($B$3+ROWS(D$2:D2)*7-7))
E2:E13E2=$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

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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))

Cell Formulas
RangeFormula
B1B1=TODAY()
D2:D13D2=IF(VALUE($B$3)+ROWS(D$2:D2)*7-7>VALUE($C$3),"",ISOWEEKNUM(VALUE($B$3)+ROWS(D$2:D2)*7-7))
E2:E13E2=$B$3+ROWS(D$2:D2)*7-7
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
That suggests that B3 & C3 are text rather than numbers, is that the case?
As you now have 365 you could use
Excel Formula:
=ISOWEEKNUM(B3+SEQUENCE(ROUNDUP((C3-B3)/7,0),,0,7))
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
718
Office Version
  1. 365
Platform
  1. Windows
That suggests that B3 & C3 are text rather than numbers, is that the case?
As you now have 365 you could use
Excel Formula:
=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! :giggle:
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,883
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,174
Messages
5,640,593
Members
417,154
Latest member
gm_jagath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top