Counting Formula

[BrettOlbrys1]

Based on a week date, I want to assign a "1" to that date. The dates greater than that date, I want the value to count up by +1 and the dates less than that date, I want them to count down by -1. My file is not static though because based on when the file is opened, the numbers assigned to the static dates will move. I need a formula that will assign the numbers above the dates.

If today was 4/19
-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14
If today was 5/10
-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14
If today was 5/24
-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4
3/8	3/15	3/22	3/29	4/5	4/12	4/19	4/26	5/3	5/10	5/17	5/24	5/31	6/7	6/14

 

[johnnyL]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11
2	23-Feb-21	2-Mar-21	9-Mar-21	16-Mar-21	23-Mar-21	30-Mar-21	6-Apr-21	13-Apr-21	20-Apr-21	27-Apr-21	4-May-21	11-May-21	18-May-21	25-May-21	1-Jun-21	8-Jun-21	15-Jun-21	22-Jun-21	29-Jun-21	6-Jul-21	13-Jul-21	20-Jul-21	27-Jul-21
3
4	-11	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11
5	16-Feb-21	23-Feb-21	2-Mar-21	9-Mar-21	16-Mar-21	23-Mar-21	30-Mar-21	6-Apr-21	13-Apr-21	20-Apr-21	27-Apr-21	4-May-21	11-May-21	18-May-21	25-May-21	1-Jun-21	8-Jun-21	15-Jun-21	22-Jun-21	29-Jun-21	6-Jul-21	13-Jul-21	20-Jul-21
6
7
Sheet1

Cell Formulas
Range	Formula
A1:W1	A1	=(A2-TODAY())/7
A4:W4	A4	=(A5-TODAY())/7+1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BrettOlbrys1]

Let's try again. It is now 12 May here.

BTW, you never answered my question:


Here is (part of) my new Raw data sheet

BrettOlbrys1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2	6/01/21	13/01/21	20/01/21	27/01/21	3/02/21	10/02/21	17/02/21	24/02/21	3/03/21	10/03/21	17/03/21	24/03/21	31/03/21	7/04/21	14/04/21	21/04/21	28/04/21	5/05/21	12/05/21	19/05/21	26/05/21
3	data 1	data 2	data 3	data 4	data 5	data 6	data 7	data 8	data 9	data 10	data 11	data 12	data 13	data 14	data 15	data 16	data 17	data 18	data 19	data 20	data 21
4	other 1	other 2	other 3	other 4	other 5	other 6	other 7	other 8	other 9	other 10	other 11	other 12	other 13	other 14	other 15	other 16	other 17	other 18	other 19	other 20	other 21
Raw data

Cell Formulas
Range	Formula
B2:U2	B2	=A2+7

The -84 in the formula below is the number of days to allow for 12 weeks of results.

BrettOlbrys1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	24/02/21	3/03/21	10/03/21	17/03/21	24/03/21	31/03/21	7/04/21	14/04/21	21/04/21	28/04/21	5/05/21	12/05/21
2	data 8	data 9	data 10	data 11	data 12	data 13	data 14	data 15	data 16	data 17	data 18	data 19
3	other 8	other 9	other 10	other 11	other 12	other 13	other 14	other 15	other 16	other 17	other 18	other 19
4
12 weeks

Cell Formulas
Range	Formula
A1:L3	A1	=FILTER('Raw data'!A2:AZ4,('Raw data'!A2:AZ2<=TODAY())*('Raw data'!A2:AZ2>TODAY()-84),"")
Dynamic array formulas.

Thanks Peter, this was the solution!

 

[Peter_SSs]

Thanks Peter, this was the solution!

You're welcome. Skip the week numbering altogether.
Thanks for the follow-up.

 

[BrettOlbrys1]

Hello again. I'm back for another question on this same topic. I want to assign the number "1" to the CURRENT WEEK and it needs to be a WHOLE number, not a number with decimals because I am looking for an exact match to "1". The problem I have run into is that I have a range of weekly dates starting from 1/4/21 through 1/2/23 (each week range is 7 days greater than the previous week) and it is assigning "1" to the week of 2/1/21, not 1/31/22. The formula I have is:

=IFERROR(CHOOSE((WEEKNUM(CY5,2)-WEEKNUM(TODAY(),2)+2),0,1,2),"")

Row 5 contains the range of dates.

I tried another formula, but it gives me decimals across the assigned numbers, but I need whole numbers to do a match: =(CY5-TODAY())/7+1

What formula do I need that will assign a "1" to the current week where the "1" or whatever number (-1, 0, 1, 2, 3, etc.) will be WHOLE numbers?

Thanks

Brett

 

[Peter_SSs]

It is a long time since the original question and your current question seems to have little to do with the post that you said was the solution to that.

Therefore could we have some fresh sample data and expected results for the new query (preferably with XL2BB) and explain again in relation to the new sample data?