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

 

[kweaver]

Is this what you mean?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	If today was	19-Apr
2	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9
3	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
4
5
6	If today was	10-May
7	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
8	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
9
10
11	If today was	24-May
12	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4
13	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
Sheet8

Cell Formulas
Range	Formula
B12:O12,B7:O7,B2:O2	B2	=A2+1

 

[BrettOlbrys1]

Is this what you mean?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	If today was	19-Apr
2	-5	-4	-3	-2	-1	0	1	2	3	4	5	6	7	8	9
3	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
4
5
6	If today was	10-May
7	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
8	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
9
10
11	If today was	24-May
12	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4
13	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
Sheet8

Cell Formulas
Range	Formula
B12:O12,B7:O7,B2:O2	B2	=A2+1

No, because you are starting with a hard-coded values in column A. Someone previously gave me a formula for a month counter, but I can't seem to figure out how to adjust it for the weeks. The month formula they provided me was:

=IF(AJ98<EOMONTH(TODAY(),-1)+1,-DATEDIF(AJ98,EOMONTH(TODAY(),-1)+1,"m")+1,DATEDIF(EOMONTH(TODAY(),-1)+1,AJ98,"m")+1)

I need something similar for weeks.

 

[johnnyL]

@BrettOlbrys1 I think a little more info would help the members help you.

Are you going to place the '1' in the desired column cell, or are you wanting that to be determined by a cell that has a date that you wish to match in your table of values? If so, will this cell that has the desired date to match be changed, or is it a set it once and forget it?

 

[kweaver]

In addition to JohnnyL's question, is the start date in B3, for example, an entry you'll make then increments by a week at a time?

So, could it look like this?

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	If today was	19-Apr
2	-2	-1	0	1	2	3	4	5	6	7	8	9	10	11	12
3	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
4
5
6	If today was	17-May
7	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4	5	6
8	3/8	15-Mar	22-Mar	29-Mar	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun
9
10
11	If today was	24-May
12	-10	-9	-8	-7	-6	-5	-4	-3	-2	-1	0	1	2	3	4
13	3/8	5-Apr	12-Apr	19-Apr	26-Apr	3-May	10-May	17-May	24-May	31-May	7-Jun	14-Jun	21-Jun	28-Jun	5-Jul
Sheet8

Cell Formulas
Range	Formula
B12:O12,B7:O7,B2:O2	B2	=A2+1
C13:O13,C8:O8,C3:O3	C3	=7+B3

 

[johnnyL]

@kweaver That doesn't look right. The OP stated to have a '1' over the 'If today was' date. You now have a '4' over the the first date of 19-Apr, etc.

 

[kweaver]

That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.

 

[Marcelo Branco]

That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.

Maybe using the formula below in A2
=1-MATCH(B1,B3:O3,0)

I don't understand what A3 (3/8) means (???)

M.

 

[Peter_SSs]

I don't understand what A3 (3/8) means (???)

8 March?


@BrettOlbrys1
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Your dates are all one week apart and all of your examples have "today's date" as one of the values in the row. What should happen if today's date is not actually in the list?

Assuming today's date is in the list, here are two possible methods. The first requires Excel 365 and only needs to be entered in the first cell.
The second - should work in all versions - needs to be copied across the row.

Todays' date for me when posting this is 11 May

21 05 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	-5	-4	-3	-2	-1	0	1	2	3	4	5
2	30-Mar-21	06-Apr-21	13-Apr-21	20-Apr-21	27-Apr-21	04-May-21	11-May-21	18-May-21	25-May-21	01-Jun-21	08-Jun-21
3
4	-5	-4	-3	-2	-1	0	1	2	3	4	5
5	30-Mar-21	06-Apr-21	13-Apr-21	20-Apr-21	27-Apr-21	04-May-21	11-May-21	18-May-21	25-May-21	01-Jun-21	08-Jun-21
Count Dates

Cell Formulas
Range	Formula
A1:K1	A1	=SEQUENCE(,COUNT(A2:K2),(A2-TODAY())/7+1)
A4:K4	A4	=(A5-TODAY())/7+1
Dynamic array formulas.

 

[johnnyL]

That's what confuses me. If A2 = -5, that's one thing, but if A2 = -2, that's different because the OP also said to count + 1 from that value.
So, at least I need some clarification what it's supposed to be.

Hopefully @BrettOlbrys1 can correct me if I am wrong here in my assumptions to the intent that the OP indicated ...

1) First row, we have a 'If today was' and then some date which is to be used for comparison to the dates that are in the 3rd row.
2) Below that, we have 2 rows, row 2 is to be used as counter values before and after a matched date found in row 3.
3) When a match is found in row3 to the date in row 1, assign row 2 in that matching column to '1'.
4) Increment/decrement the surrounding cells in row2 in respect to the offset of the '1'.

So I further assume, if the date in row1 is changed, I would assume a different column/row would have to be assigned the '1', and left/right cells to that would be incremented/decremented.

Repeat process for additional rows/columns.

15 columns wide of dates and counter values