Rotating dates

Luigi802

Board Regular
Joined
Oct 16, 2014
Messages
77
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

Date#
=DATE(2014,9,26)+CEILING(TODAY()-DATE(2014,9,26),7)1
=D2+72
=D3+71
=D4+72
=D5+71
=D6+72
=D7+71
=D8+72
=D9+71
=D10+72
=D11+71
=D12+72
=D13+71
=D14+72
=D15+71
=D16+72
=D17+71
=D18+72
=D19+71
=D20+72

<tbody>
</tbody>


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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,015
Office Version
2013
Platform
Windows
Luigi802,

Maybe....
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">26/12/2014</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">02/01/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">09/01/2015</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">16/01/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">23/01/2015</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">30/01/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">06/02/2015</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">13/02/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">20/02/2015</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">27/02/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">06/03/2015</td><td style="text-align: center;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=ISODD(<font color="Blue">INT(<font color="Red">D2/7</font>)</font>)+1</td></tr></tbody></table></td></tr></table><br />
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">19/12/2014</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">26/12/2014</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">02/01/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">09/01/2015</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">16/01/2015</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">23/01/2015</td><td style="text-align: center;;">2</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br />

Hope that helps.
 

Luigi802

Board Regular
Joined
Oct 16, 2014
Messages
77
Luigi802,

Maybe....
Excel 2007
DE
226/12/20142
302/01/20151
409/01/20152
516/01/20151
623/01/20152
730/01/20151
806/02/20152
913/02/20151
1020/02/20152
1127/02/20151
1206/03/20152

<tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
E2=ISODD(INT(D2/7))+1

<tbody>
</tbody>

<tbody>
</tbody>


Excel 2007
DE
219/12/20141
326/12/20142
402/01/20151
509/01/20152
616/01/20151
723/01/20152

<tbody>
</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.
 
Last edited:

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,015
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Oct 16, 2014
Messages
77
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,926
Messages
5,447,336
Members
405,447
Latest member
WPY

This Week's Hot Topics

Top