Last 15 weeks values

[buer4499]

Hello,

Is there a formula to pull the last 15 weeks based on the day of the week and the current date.

For example, today is 04/08/2021 and I want to pull the past 15 weeks Mondays.

Data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Day	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	Date	07/06/2021	08/06/2021	09/06/2021	10/06/2021	11/06/2021	12/06/2021	13/06/2021	14/06/2021	15/06/2021	16/06/2021	17/06/2021	18/06/2021	19/06/2021	20/06/2021	21/06/2021	22/06/2021	23/06/2021	24/06/2021	25/06/2021	26/06/2021	27/06/2021
3	00:00:00	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%
4	01:00:00	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%
5	02:00:00	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%
6	03:00:00	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%
7	04:00:00	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%
8	05:00:00	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%
9	06:00:00	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%
10	07:00:00	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%
11	08:00:00	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%
12	09:00:00	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%
13	10:00:00	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%
14	11:00:00	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%
15	12:00:00	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%
16	13:00:00	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%
17	14:00:00	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%
18	15:00:00	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%
19	16:00:00	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%
20	17:00:00	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%
21	18:00:00	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%
22	19:00:00	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%
23	20:00:00	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%
24	21:00:00	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%
25	22:00:00	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%
26	23:00:00	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%
Sheet1

Cell Formulas
Range	Formula
C2:V2	C2	=B2+1

Layout required:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Today	04/08/2021
2
3		Monday	Week 15	Week 14	Week 13	Week 12	Week 11	Week 10	Week 9	Week 8	Week 7	Week 6	Week 5	Week 4	Week 3	Week 2	Week 1
4		00:00:00
5		01:00:00
6		02:00:00
7		03:00:00
8		04:00:00
9		05:00:00
10		06:00:00
11		07:00:00
12		08:00:00
13		09:00:00
14		10:00:00
15		11:00:00
16		12:00:00
17		13:00:00
18		14:00:00
19		15:00:00
20		16:00:00
21		17:00:00
22		18:00:00
23		19:00:00
24		20:00:00
25		21:00:00
26		22:00:00
27		23:00:00
28
Sheet2

 

[jorismoerings]

Hi,

Something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Today	4-8-2021
2
3		Monday	Week 15	Week 14	Week 13	Week 12	Week 11	Week 10	Week 9	Week 8	Week 7	Week 6	Week 5	Week 4	Week 3	Week 2	Week 1
4		00:00:00	0%	0%	0%	0%	0%	0%	2%	2%	0%	0%	0%	0%	0%	0%	0%
5		01:00:00	0%	0%	0%	0%	0%	0%	2%	2%	0%	0%	0%	0%	0%	0%	0%
6		02:00:00	0%	0%	0%	0%	0%	0%	2%	1%	0%	0%	0%	0%	0%	0%	0%
7		03:00:00	0%	0%	0%	0%	0%	0%	1%	1%	0%	0%	0%	0%	0%	0%	0%
8		04:00:00	0%	0%	0%	0%	0%	0%	1%	1%	0%	0%	0%	0%	0%	0%	0%
9		05:00:00	0%	0%	0%	0%	0%	0%	1%	1%	0%	0%	0%	0%	0%	0%	0%
10		06:00:00	0%	0%	0%	0%	0%	0%	2%	2%	0%	0%	0%	0%	0%	0%	0%
11		07:00:00	0%	0%	0%	0%	0%	0%	3%	3%	0%	0%	0%	0%	0%	0%	0%
12		08:00:00	0%	0%	0%	0%	0%	0%	5%	5%	0%	0%	0%	0%	0%	0%	0%
13		09:00:00	0%	0%	0%	0%	0%	0%	6%	5%	0%	0%	0%	0%	0%	0%	0%
14		10:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
15		11:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
16		12:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
17		13:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
18		14:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
19		15:00:00	0%	0%	0%	0%	0%	0%	6%	7%	0%	0%	0%	0%	0%	0%	0%
20		16:00:00	0%	0%	0%	0%	0%	0%	7%	7%	0%	0%	0%	0%	0%	0%	0%
21		17:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
22		18:00:00	0%	0%	0%	0%	0%	0%	6%	6%	0%	0%	0%	0%	0%	0%	0%
23		19:00:00	0%	0%	0%	0%	0%	0%	5%	5%	0%	0%	0%	0%	0%	0%	0%
24		20:00:00	0%	0%	0%	0%	0%	0%	4%	5%	0%	0%	0%	0%	0%	0%	0%
25		21:00:00	0%	0%	0%	0%	0%	0%	4%	5%	0%	0%	0%	0%	0%	0%	0%
26		22:00:00	0%	0%	0%	0%	0%	0%	3%	3%	0%	0%	0%	0%	0%	0%	0%
27		23:00:00	0%	0%	0%	0%	0%	0%	2%	3%	0%	0%	0%	0%	0%	0%	0%
Sheet4

Cell Formulas
Range	Formula
C4:Q27	C4	=SUMPRODUCT(--(Sheet3!$B$1:$V$1=Sheet4!$B$3),--(WEEKNUM(Sheet3!$B$2:$V$2+0)=WEEKNUM($B$1)+1-RIGHT(C$3,2)),Sheet3!B3:V3)

Notes:

1. For this formula to work there must always be a space between the word "week" and the actual weeknumber;
2. Week 1 in the report lay out is assumed to be the current week.

 

[buer4499]

Hey, I used your formula and it's returning a 0% for every value.

15 Week Rolling Dashboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Today	05/08/2021
2
3		Monday	19/04/2021	26/04/2021	03/05/2021	10/05/2021	24/05/2021	17/05/2021	31/05/2021	07/06/2021	14/06/2021	21/06/2021	28/06/2021	05/07/2021	12/07/2021	19/07/2021	26/07/2021
4		00:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
5		01:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
6		02:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
7		03:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
8		04:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
9		05:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
10		06:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
11		07:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
12		08:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
13		09:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
14		10:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
15		11:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
16		12:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
17		13:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
18		14:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
19		15:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
20		16:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
21		17:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
22		18:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
23		19:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
24		20:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
25		21:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
26		22:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
27		23:00:00	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%	0.00%
Sheet2

Cell Formulas
Range	Formula
B1	B1	=TODAY()
C3	C3	=$B$1-WEEKDAY($B$1,3)-105
D3	D3	=$B$1-WEEKDAY($B$1,3)-98
E3	E3	=$B$1-WEEKDAY($B$1,3)-91
F3	F3	=$B$1-WEEKDAY($B$1,3)-84
G3	G3	=$B$1-WEEKDAY($B$1,3)-70
H3	H3	=$B$1-WEEKDAY($B$1,3)-77
I3	I3	=$B$1-WEEKDAY($B$1,3)-63
J3	J3	=$B$1-WEEKDAY($B$1,3)-56
K3	K3	=$B$1-WEEKDAY($B$1,3)-49
L3	L3	=$B$1-WEEKDAY($B$1,3)-42
M3	M3	=$B$1-WEEKDAY($B$1,3)-35
N3	N3	=$B$1-WEEKDAY($B$1,3)-28
O3	O3	=$B$1-WEEKDAY($B$1,3)-21
P3	P3	=$B$1-WEEKDAY($B$1,3)-14
Q3	Q3	=$B$1-WEEKDAY($B$1,3)-7
C4:Q27	C4	=SUMPRODUCT(--(Sheet1!$B$1:$V$1=Sheet2!$B$3),--(WEEKNUM(Sheet1!$B$2:$V$2+0)=WEEKNUM($B$1)+1-RIGHT(C$3,2)),Sheet1!B3:V3)

 

[buer4499]

Data:

15 Week Rolling Dashboard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Day	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday	Sunday
2	Date	07/06/2021	08/06/2021	09/06/2021	10/06/2021	11/06/2021	12/06/2021	13/06/2021	14/06/2021	15/06/2021	16/06/2021	17/06/2021	18/06/2021	19/06/2021	20/06/2021	21/06/2021	22/06/2021	23/06/2021	24/06/2021	25/06/2021	26/06/2021	27/06/2021
3	00:00:00	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%	1.9%	1.9%	1.9%	1.9%	1.9%	2.1%	2.1%
4	01:00:00	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%	1.6%	1.6%	1.6%	1.6%	1.6%	1.7%	1.7%
5	02:00:00	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%
6	03:00:00	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%	1.3%
7	04:00:00	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%	1.2%	1.2%	1.2%	1.2%	1.2%	1.3%	1.3%
8	05:00:00	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%	1.4%	1.4%	1.4%	1.4%	1.4%	1.5%	1.5%
9	06:00:00	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%	2.2%
10	07:00:00	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%	3.2%	3.2%	3.2%	3.2%	3.2%	3.4%	3.4%
11	08:00:00	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%	4.5%	4.5%	4.5%	4.5%	4.5%	4.7%	4.7%
12	09:00:00	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%	5.4%	5.4%	5.4%	5.4%	5.4%	5.7%	5.7%
13	10:00:00	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%	5.7%	5.7%	5.7%	5.7%	5.7%	6.1%	6.1%
14	11:00:00	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%	5.8%	5.8%	5.8%	5.8%	5.8%	6.3%	6.3%
15	12:00:00	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%	5.9%	5.9%	5.9%	5.9%	5.9%	6.4%	6.4%
16	13:00:00	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%	6.1%	6.1%	6.1%	6.1%	6.1%	6.3%	6.3%
17	14:00:00	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%	6.1%	6.1%	6.1%	6.1%	6.1%	6.2%	6.2%
18	15:00:00	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%	6.5%	6.5%	6.5%	6.5%	6.5%	6.2%	6.2%
19	16:00:00	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%	6.6%	6.6%	6.6%	6.6%	6.6%	6.5%	6.5%
20	17:00:00	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%	6.3%
21	18:00:00	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%	5.9%	5.9%	5.9%	5.9%	5.9%	6.1%	6.1%
22	19:00:00	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%	5.4%
23	20:00:00	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%	5.1%	5.1%	5.1%	5.1%	5.1%	4.4%	4.4%
24	21:00:00	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%	4.5%	4.5%	4.5%	4.5%	4.5%	3.6%	3.6%
25	22:00:00	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%	3.5%	3.5%	3.5%	3.5%	3.5%	2.7%	2.7%
26	23:00:00	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%	2.6%	2.6%	2.6%	2.6%	2.6%	2.1%	2.1%
Sheet1

Cell Formulas
Range	Formula
C2:V2	C2	=B2+1

 

[jorismoerings]

Hi,

Yep, that's correct and caused by the lay out change.
In your initial post line 3 of the summary sheet contained week numbers.

In my notes under my post I explicitly mentioned note 1

Notes:

1. For this formula to work there must always be a space between the word "week" and the actual weeknumber;
2. Week 1 in the report lay out is assumed to be the current week.

Change it to this and it should work:

Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Today	5-8-2021
2
3		Monday	19-4-2021	26-4-2021	3-5-2021	10-5-2021	24-5-2021	17-5-2021	31-5-2021	7-6-2021	14-6-2021	21-6-2021	28-6-2021	5-7-2021	12-7-2021	19-7-2021	26-7-2021
4		00:00:00	0%	0%	0%	0%	0%	0%	0%	2%	2%	2%	0%	0%	0%	0%	0%
5		01:00:00	0%	0%	0%	0%	0%	0%	0%	2%	2%	2%	0%	0%	0%	0%	0%
6		02:00:00	0%	0%	0%	0%	0%	0%	0%	1%	1%	1%	0%	0%	0%	0%	0%
7		03:00:00	0%	0%	0%	0%	0%	0%	0%	1%	1%	1%	0%	0%	0%	0%	0%
8		04:00:00	0%	0%	0%	0%	0%	0%	0%	1%	1%	1%	0%	0%	0%	0%	0%
9		05:00:00	0%	0%	0%	0%	0%	0%	0%	1%	1%	1%	0%	0%	0%	0%	0%
10		06:00:00	0%	0%	0%	0%	0%	0%	0%	2%	2%	2%	0%	0%	0%	0%	0%
11		07:00:00	0%	0%	0%	0%	0%	0%	0%	3%	3%	3%	0%	0%	0%	0%	0%
12		08:00:00	0%	0%	0%	0%	0%	0%	0%	5%	5%	5%	0%	0%	0%	0%	0%
13		09:00:00	0%	0%	0%	0%	0%	0%	0%	5%	5%	5%	0%	0%	0%	0%	0%
14		10:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
15		11:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
16		12:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
17		13:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
18		14:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
19		15:00:00	0%	0%	0%	0%	0%	0%	0%	7%	7%	7%	0%	0%	0%	0%	0%
20		16:00:00	0%	0%	0%	0%	0%	0%	0%	7%	7%	7%	0%	0%	0%	0%	0%
21		17:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
22		18:00:00	0%	0%	0%	0%	0%	0%	0%	6%	6%	6%	0%	0%	0%	0%	0%
23		19:00:00	0%	0%	0%	0%	0%	0%	0%	5%	5%	5%	0%	0%	0%	0%	0%
24		20:00:00	0%	0%	0%	0%	0%	0%	0%	5%	5%	5%	0%	0%	0%	0%	0%
25		21:00:00	0%	0%	0%	0%	0%	0%	0%	5%	5%	5%	0%	0%	0%	0%	0%
26		22:00:00	0%	0%	0%	0%	0%	0%	0%	3%	3%	3%	0%	0%	0%	0%	0%
27		23:00:00	0%	0%	0%	0%	0%	0%	0%	3%	3%	3%	0%	0%	0%	0%	0%
Sheet2

Cell Formulas
Range	Formula
B1	B1	=TODAY()
C3	C3	=$B$1-WEEKDAY($B$1,3)-105
D3	D3	=$B$1-WEEKDAY($B$1,3)-98
E3	E3	=$B$1-WEEKDAY($B$1,3)-91
F3	F3	=$B$1-WEEKDAY($B$1,3)-84
G3	G3	=$B$1-WEEKDAY($B$1,3)-70
H3	H3	=$B$1-WEEKDAY($B$1,3)-77
I3	I3	=$B$1-WEEKDAY($B$1,3)-63
J3	J3	=$B$1-WEEKDAY($B$1,3)-56
K3	K3	=$B$1-WEEKDAY($B$1,3)-49
L3	L3	=$B$1-WEEKDAY($B$1,3)-42
M3	M3	=$B$1-WEEKDAY($B$1,3)-35
N3	N3	=$B$1-WEEKDAY($B$1,3)-28
O3	O3	=$B$1-WEEKDAY($B$1,3)-21
P3	P3	=$B$1-WEEKDAY($B$1,3)-14
Q3	Q3	=$B$1-WEEKDAY($B$1,3)-7
C4:Q27	C4	=SUMPRODUCT(--(Sheet1!$B$1:$V$1=Sheet2!$B$3),--(WEEKNUM(Sheet1!$B$2:$V$2+0)=WEEKNUM($B$1)-(18-COLUMN())),Sheet1!$B3:$V3)