Week Of The Month

[Excel Fox]

If you wanted to know the week number in a year, you'd just have to use WEEKNUM(serial_num,[return_type])

If you wanted to know the week number in a month, you could use the following formula

=IF(MONTH(A1)>1,WEEKNUM(A1,2)-WEEKNUM(DATE(YEAR(A1),MONTH(A1),0),2)+1,WEEKNUM(A1 ,2))

You could make it shorter with the EOMONTH formula as below

=IF(MONTH(A1)>1,WEEKNUM(A1,2)-WEEKNUM(EOMONTH(A1,-1),2)+1,WEEKNUM(A1,2))

For those who are inclined to the mathematics of it, you could use an even intuitive formula

=ROUNDUP((A1-EOMONTH(A1,-1))/7,0)

[Rick Rothstein]

Your ROUNDUP formula does not produce the same results as your other two formulas. The first two formulas appear to be calculating the breakpoint between the first week and the second week at the first Monday in the month. So, any days before the first Monday are part of Week #1 and the first Monday plus the next 6 days make up Week #2, the next 7 days after that make up Week #3, and so on. Your ROUNDUP formula, on the other hand, simply considers the first 7 days of the month to be in Week #1, then next 7 days being in Week #2 and so on. As an aside, your ROUNDUP formula can be simplified to this...

=ROUNDUP(DAY(A1)/7,0)

[Excel Fox]

You got that right Rick.... a little too confident for comfort from my part I guess.