Week Number in the Month - Weekdays only

[SDixon]

Hi,

I have scoured the internet looking for some help with this, but cannot find the answer to my particular problem. I can get the week number in the month, but I only want it for working days (ie. October 31st should be in week 5, since the 1st & 2nd of Oct are a Saturday & Sunday).

I have a list of dates in Excel & I need to know what week number they are in for each month. I want to ignore weekends, so October 1st can be week 0 for instance.

Cab anyone help me with this?

Thanks,

Stacy

 

[Trevor G]

Try this out

Excel Workbook
	C	D	E
1	date	Week Number	Week Number
2	01/08/2011	1	1
3	11/08/2011	2	2
4	21/08/2011	3	3
5	31/08/2011	5	5
Sheet1

 

[Pauljj]

If your dates in October are in cell E4 (for example) then in F4 add

=WEEKDAY(E4)

and in G4 add

=IF(OR(F4=7,F4=1),"",COUNTIF($F$4:F4,WEEKDAY(E4)))

and drag the formula down

 

[SDixon]

Hi Trevor,

I have a formula similar to that, which I cannot get to understand that a Monday means a new week. If you use the formula, then skip ahead to the 7th of November it will say that it's in week one, when it's really week 2.

I just can't find a way to tell Excel that there are only 5 days in a week, week one starts on the first weekday of the month, and the following week will always starts on a Monday, then understand to restart that process every month. Which should make the ouput value never higher than five for every date in the year.

I think I'm asking too much :-/

 

[Boller]

<TABLE style="WIDTH: 557pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=743><COLGROUP><COL style="WIDTH: 557pt; mso-width-source: userset; mso-width-alt: 27172" width=743><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 557pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=17 width=743>=IF(AND(WEEKDAY(A1)=1,MONTH(A1)<>MONTH(A2)),1,IF(MONTH(A1)<>MONTH(A2),1,IF(WEEKDAY(A1)=1,B1+1,B1)))</TD></TR></TBODY></TABLE>

 

[Boller]

By way of further explanation :

- the dates are in column A starting in A1

- in B1 enter either the weekday number of the date in A1 or the formula =WEEKDAY(A1,2)

- enter the formula previously posted in B2 and fill down.

Perhaps a "cleaner" way is to build on a separate sheet a table for the whole year (using the steps above). Once the table is created, column B could be converted to value.
The weekday numbers could then be retrieved with a VLOOKUP formula.

 

[MikeWx]

Stacy,

I have tested the following formula within and across multiple months, and I think it satisfies your need. It blanks out Saturdays and Sundays, and gives the week number for all other days.

=IF(OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1),"",ROUNDUP((DAY(A1)+CHOOSE(WEEKDAY((A1-DAY(A1)+1)),0,0,1,2,3,4,0))/7,0))

Mike

 

[SDixon]

MikeWx - Worked like a charm, that you very much!

Pauljj, Trevor, Boller - Thank you all for your help. I have been testing & melding your suggestions to coem up with the solution. I was an IF statement away from perfecting it, when I got MikeWx's post, which was much cleaner than the mess I made!

Thank you everyone, you are greatly appreciated

 

[MikeWx]

Thank you for letting me know.

Mike

 

[barry houdini]

This formula should also give the same result

=IF(WEEKDAY(A2,2)<6,INT((15-WEEKDAY(A2+1)+DAY(A2)-2)/7),"")