459,474 Members | 1,267 Online
Need help? Post your question and get tips & solutions from a community of 459,474 IT Pros & Developers. It's quick & easy.

# Calculating the Week Number in a given month

 P: 30 I would like to know if there is a function available to determine the week number in any given month. For example March 20th, 2010 would be Week #3 Mar 20 '10 #1

To the best of my knowledge, there is no Function that will return the Week of the Month for a given Date. I through together a Custom Function that you can test, but I've had no time to test it myself and I am not sure of its accuracy.
Expand|Select|Wrap|Line Numbers
1. Public Function fCalcWeekOfMonth(dteDate As Date) As Byte
2.   fCalcWeekOfMonth = DatePart("ww", dteDate) - _
3.                     DatePart("ww", DateSerial(Year(dteDate), Month(dteDate), 1)) + 1
4. End Function
Expand|Select|Wrap|Line Numbers
1. MsgBox fCalcWeekOfMonth(#3/20/2010#)
returns
Expand|Select|Wrap|Line Numbers
1. 3

5 Replies

 Expert 5K+ P: 8,701 To the best of my knowledge, there is no Function that will return the Week of the Month for a given Date. I through together a Custom Function that you can test, but I've had no time to test it myself and I am not sure of its accuracy. Expand|Select|Wrap|Line Numbers Public Function fCalcWeekOfMonth(dteDate As Date) As Byte   fCalcWeekOfMonth = DatePart("ww", dteDate) - _                     DatePart("ww", DateSerial(Year(dteDate), Month(dteDate), 1)) + 1 End Function Expand|Select|Wrap|Line Numbers MsgBox fCalcWeekOfMonth(#3/20/2010#) returns Expand|Select|Wrap|Line Numbers 3 Mar 20 '10 #2

 Expert Mod 15k+ P: 31,768 I don't believe there is a built-in function available anywhere. You could write your own, but you'd need to be more clear as to what exactly constituted the first week before you started. Mar 20 '10 #3

 Expert 2.5K+ P: 3,532 The OP's example would indicate, to me, that he wanted it just as it would appear on a printed calendar. I gave the code a pretty good test run, including Februarys with both 28 and 29 days, and could find no problems with it. Linq ;0)> Mar 20 '10 #4

 P: 30 Thank you so much ADezii for your prompt response. Mar 24 '10 #5

 Expert 5K+ P: 8,701 You are quite welcome. Mar 24 '10 #6