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:
Layout required:
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 |