Rotating dates

[Luigi802]

As you see below I have a list of dates that rotates ever week, the numbers in the right column are supposed to rotate with them. I'm pretty stuck, not sure how to even start this. the formulas I used are in the second list

Date #
19 Dec 14 1
26 Dec 14 2
02 Jan 15 1
09 Jan 15 2
16 Jan 15 1
23 Jan 15 2
30 Jan 15 1
06 Feb 15 2
13 Feb 15 1
20 Feb 15 2
27 Feb 15 1
06 Mar 15 2
13 Mar 15 1
20 Mar 15 2
27 Mar 15 1
03 Apr 15 2
10 Apr 15 1
17 Apr 15 2
24 Apr 15 1
01 May 15 2

[TABLE="width: 384"]
<tbody>[TR]
[TD]Date[/TD]
[TD]#[/TD]
[/TR]
[TR]
[TD]=DATE(2014,9,26)+CEILING(TODAY()-DATE(2014,9,26),7)[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D2+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D3+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D4+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D5+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D6+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D7+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D8+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D9+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D10+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D11+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D12+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D13+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D14+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D15+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D16+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D17+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D18+7[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]=D19+7[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]=D20+7[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]


So next week after the 19th of DEC that cell will change to 26 DEC but I want the 2 which is next to the 26 Dec to stay with that date i.e. The list starting 1, 2, 1, 2, will switch every week from starting with 1, 2, 1, 2, to starting 2, 1, 2, 1. Basically I just want the 1's to change to 2's and the 2's change to 1's every week. Does that make sense?

 

[Snakehips]

Luigi802,

Maybe....

Excel 2007
	D	E
2	26/12/2014	2
3	02/01/2015	1
4	09/01/2015	2
5	16/01/2015	1
6	23/01/2015	2
7	30/01/2015	1
8	06/02/2015	2
9	13/02/2015	1
10	20/02/2015	2
11	27/02/2015	1
12	06/03/2015	2
Sheet3

Cell Formulas
Range	Formula
E2	=ISODD(INT(D2/7))+1

Excel 2007
	D	E
2	19/12/2014	1
3	26/12/2014	2
4	02/01/2015	1
5	09/01/2015	2
6	16/01/2015	1
7	23/01/2015	2
Sheet3

Hope that helps.

 

[Luigi802]

Luigi802,

Maybe....
Excel 2007

	D	E

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]26/12/2014[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]02/01/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]09/01/2015[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]16/01/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]30/01/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]06/02/2015[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]13/02/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]20/02/2015[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]27/02/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]06/03/2015[/TD]
[TD="align: center"]2[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #E0E0F0"]E2[/TH]
[TD="align: left"]=ISODD(INT(D2/7))+1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Excel 2007

	D	E

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]19/12/2014[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]26/12/2014[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]02/01/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]09/01/2015[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]16/01/2015[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]23/01/2015[/TD]
[TD="align: center"]2[/TD]

</tbody>

Sheet3​

Hope that helps.

Sorry I guess I could have been a little more clear. OK That works if I was actually using the numbers 1 & 2 I put that just to simplify my question. Although I could still use this if I reference another column, what I really want is for it to switch from "#A" to "#B", like say #A is 600 and #B is 1600 instead of 1 and 2. Thanks for this though at least I have something that will work for now but I'd really like to not have to use another column.

 

[Snakehips]

Then try, in E2

=IF(ISODD(INT(D2/7)),600,1600)

If you don't wish to hard code the numbers then make reference to a pair of cells where you can set the numbers.

 

[Luigi802]

Then try, in E2

=IF(ISODD(INT(D2/7)),600,1600)

If you don't wish to hard code the numbers then make reference to a pair of cells where you can set the numbers.

I do wish to hard code the numbers, I don't want to have to refer to another cell.