Listing weeks with different names

[cogumelo]

Hi, can anyone please help me out to solve this one?

I need a function that will change every 6 days. For instance from the 3rd to the 9th it will be W1, from the 10th to the 16th it will be W2 and so on...

day description
03-10-2011 W1
04-10-2011 W1
05-10-2011 W1
06-10-2011 W1
07-10-2011 W1
08-10-2011 W1
09-10-2011 W1
10-10-2011 W2
11-10-2011 W2
12-10-2011 W2
13-10-2011 W2
14-10-2011 W2
15-10-2011 W2
16-10-2011 W2
17-10-2011 W3
18-10-2011 W3
19-10-2011 W3
20-10-2011 W3
21-10-2011 W3
22-10-2011 W3
23-10-2011 W3

thanx in advance for helping out

 

[njimack]

Does this work for you?
B2: W1 (just enter as text)
B3: =IF(COUNTIF(B$2:B2,B2)<6,B2,LEFT(B2)&SUBSTITUTE(B2,LEFT(B2),"")+1)
(copy B3 down as far as required)

 

[cogumelo]

Hi & thanx for the quick reply,

Only getting w1 ok, the following weeks are errors #NAME?

 

[Rick Rothstein]

Only getting w1 ok, the following weeks are errors #NAME?

Slightly different formula. Same setup... put W1 in the B2, then put this formula in B3 and copy it down...

=IF(COUNTIF(B$2:B2,B2)<7,B2,"W"&MID(B2,2,2)+1)

 

[cogumelo]

hi,

still getting the same issue using both formulas


Dia njimack's formula Rick Rothstein's formula
05-09-2011 W1 W1
06-09-2011 W1 W1
07-09-2011 W1 W1
08-09-2011 W1 W1
09-09-2011 W1 W1
10-09-2011 W1 W1
11-09-2011 W1 W1
12-09-2011 #NOME? #NOME?
13-09-2011 #NOME? #NOME?

 

[njimack]

Make sure B2 doesnt contain any spaces.

 

[njimack]

Excel Workbook
	A	B
1	day	description
2	03/10/2011	W1
3	04/10/2011	W1
4	05/10/2011	W1
5	06/10/2011	W1
6	07/10/2011	W1
7	08/10/2011	W1
8	09/10/2011	W2
9	10/10/2011	W2
10	11/10/2011	W2
11	12/10/2011	W2
12	13/10/2011	W2
13	14/10/2011	W2
14	15/10/2011	W3
15	16/10/2011	W3
16	17/10/2011	W3
17	18/10/2011	W3
18	19/10/2011	W3
19	20/10/2011	W3
20	21/10/2011	W4
21	22/10/2011	W4
22	23/10/2011	W4
Sheet1

 

[markmzz]

Maybe this:

<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>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Day</td><td style="font-weight: bold;text-align: center;;">Description</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">10/3/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">10/4/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">10/5/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">10/6/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">10/7/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">10/8/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">10/9/2011</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">10/10/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">10/11/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">10/12/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">10/13/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">10/14/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">10/15/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">10/16/2011</td><td style="text-align: center;;">W2</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">10/17/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">10/18/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">10/19/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">10/20/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">10/21/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">10/22/2011</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">10/23/2011</td><td style="text-align: center;;">W3</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">Sheet1</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">B2</th><td style="text-align:left">="W"&(<font color="Blue">1+INT(<font color="Red">(<font color="Green">DAY(<font color="Purple">A2</font>)-DAY(<font color="Purple">A$2</font>)</font>)/7</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[markmzz]

Another way:

="W"&CEILING(DAY(A2)-DAY($A$2)+1,7)/7

Markmzz

 

[Rick Rothstein]

still getting the same issue using both formulas

Dia njimack's formula Rick Rothstein's formula
05-09-2011 W1 W1
06-09-2011 W1 W1
07-09-2011 W1 W1
08-09-2011 W1 W1
09-09-2011 W1 W1
10-09-2011 W1 W1
11-09-2011 W1 W1
12-09-2011 #NOME? #NOME?
13-09-2011 #NOME? #NOME?

#NOME? ???

It appears you are using a language version of Excel that does not use the MID keyword for finding text substrings. The equivalent to the MID function in your Excel version would have 3 arguments... the "Text", a "Start Number" and a "Number of Characters"... what it does is looking the "Text" startign at the character number specified by the "Start Number" and returns the "Number of Characters" specified. See if you can find that text function in your copy of Excel and use its name in place of MID in my formula. Or if you tell us the language version of your Excel, I'll try to find the correct function name for you.