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

 

[Rick Rothstein]

#NOME? ???

It appears you are using a language version of Excel that does not use the MID keyword for finding text substrings.

I Googled #NOME? and it looks like your language is Portuguese. If that is correct, then I think SEG.TEXTO is your equivalent to the MID function. If that looks familiar, then try this formula...

=IF(CONTAR.SE(B$2:B2,B2)<7,B2,"W"&SEG.TEXTO(B2,2,2)+1)

Remember, I'm just guessing here.

 

[cogumelo]

hi, thank you all for the great replies. I can't test right now, but i think all these answers will do it for me. I'll test it tomorrow morning.

Rick: Yep, it's portuguese. Your excel translator worked fine

 

[cogumelo]

hi,

this one did it!

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

and in portuguese: SE(CONTAR.SE(B$2:B2;B2)<7;B2;"W"&SEG.TEXTO(B2;2;2)+1)

markmzz,

yours worked ok for the first month but when the month changes it goes to w0

30-09-2011 w4
01-10-2011 w0


thank you all for the brilliant assistance.

 

[markmzz]

hi,

markmzz,

yours worked ok for the first month but when the month changes it goes to w0

30-09-2011 w4
01-10-2011 w0


thank you all for the brilliant assistance.

Try 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 /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</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><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;;">9/3/2011</td><td style="text-align: center;;">W1</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">9/4/2011</td><td style="text-align: center;;">W1</td><td style="text-align: center;;">W1</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">9/22/2011</td><td style="text-align: center;;">W3</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">9/23/2011</td><td style="text-align: center;;">W3</td><td style="text-align: center;;">W3</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">9/24/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;;">9/25/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;">9/26/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;">9/27/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;;">9/28/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;;">9/29/2011</td><td style="text-align: center;;">W4</td><td style="text-align: center;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;background-color: #C5D9F1;;">9/30/2011</td><td style="text-align: center;background-color: #C5D9F1;;">W4</td><td style="text-align: center;background-color: #C5D9F1;;">W4</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;background-color: #C5D9F1;;">10/1/2011</td><td style="text-align: center;background-color: #C5D9F1;;">W5</td><td style="text-align: center;background-color: #C5D9F1;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;;">10/2/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;;">10/3/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;;">10/4/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;;">10/5/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;;">10/6/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;;">10/7/2011</td><td style="text-align: center;;">W5</td><td style="text-align: center;;">W5</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;;">10/8/2011</td><td style="text-align: center;;">W6</td><td style="text-align: center;;">W6</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;;">10/9/2011</td><td style="text-align: center;;">W6</td><td style="text-align: center;;">W6</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">Sheet2</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"&1+INT(<font color="Blue">(<font color="Red">A2-A$2</font>)/7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">="W"&CEILING(<font color="Blue">A2-A$2+1,7</font>)/7</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B21</th><td style="text-align:left">="W"&1+INT(<font color="Blue">(<font color="Red">A21-A$2</font>)/7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C21</th><td style="text-align:left">="W"&CEILING(<font color="Blue">A21-A$2+1,7</font>)/7</td></tr></tbody></table></td></tr></table><br />
Markmzz