WeekOfMonth Function

[Barry Wright]

Hi Programmers,

I am trying to figure out how to create a function what will return the Week of the Month when a date is past in.

Looking at the month of July for 2004. Sunday is the first day of the week, Saturday the last day of the week.
Passing the date July 14, 2004 to the WeekOfMonth function will return a 2 because July 14, 2004 is the second Wednesday of the
Month.
Passing the date July 15, 2004 to the WeekOfMonth function will return a 3 because July 15, 2004 is the third Thursday of the Month.
Passing the date July 31, 2004 to the WeekOfMonth function will return a 5 because July 31, 2004 is the fifth Saturday of the Month.

Can anyone give me a clue of how to create this WeekOfMonth function?

Thanks,
Barry

[Wayne Morgan]

Which are you wanting? You stated that the first day of the week is Sunday,
following that logic, July 15, 2004 should return 2 and July 31, 2004 should
return 4 since the first week in July starts on Sunday, July 4, 2004. Do you
want the numbers above returned or the 3 and 5 you mentioned in your
message?

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:OA***************@news04.bloor.is.net.cable.r ogers.com...

Hi Programmers,

I am trying to figure out how to create a function what will return the Week of the Month when a date is past in.
Looking at the month of July for 2004. Sunday is the first day of the week, Saturday the last day of the week. Passing the date July 14, 2004 to the WeekOfMonth function will return a 2 because July 14, 2004 is the second Wednesday of the Month.
Passing the date July 15, 2004 to the WeekOfMonth function will return a 3 because July 15, 2004 is the third Thursday of the Month. Passing the date July 31, 2004 to the WeekOfMonth function will return a 5 because July 31, 2004 is the fifth Saturday of the Month.
Can anyone give me a clue of how to create this WeekOfMonth function?

Thanks,
Barry

[Barry Wright]

Wayne thanks for the response. The answer to you question is: I would like 3 and 5 returned.

More examples: taking the month July/2004, if you pass in July 1, 2004 the function will return 1 because it is the first Thursday
of the month, if you pass in July 8, 2004 the function will return 2 because it is the second Thursday of the month, if you pass in
July 7, 2004 it will return 1 because it is the first Wednesday of the month, if you pass in July 29, 2004 the function will return
5 because it is the fifth Thursday of the Month.

Thanks
Barry

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Bb*******************@newssvr23.news.prodigy. com...
Which are you wanting? You stated that the first day of the week is Sunday,
following that logic, July 15, 2004 should return 2 and July 31, 2004 should
return 4 since the first week in July starts on Sunday, July 4, 2004. Do you
want the numbers above returned or the 3 and 5 you mentioned in your
message?

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:OA***************@news04.bloor.is.net.cable.r ogers.com...

Hi Programmers,

I am trying to figure out how to create a function what will return the Week of the Month when a date is past in.
Looking at the month of July for 2004. Sunday is the first day of the week, Saturday the last day of the week. Passing the date July 14, 2004 to the WeekOfMonth function will return a 2 because July 14, 2004 is the second Wednesday of the Month.
Passing the date July 15, 2004 to the WeekOfMonth function will return a 3 because July 15, 2004 is the third Thursday of the Month. Passing the date July 31, 2004 to the WeekOfMonth function will return a 5 because July 31, 2004 is the fifth Saturday of the Month.
Can anyone give me a clue of how to create this WeekOfMonth function?

Thanks,
Barry

[Wayne Morgan]

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
Select Case intDate
Case 1 To 7
WeekOfMonth = 1
Case 8 To 14
WeekOfMonth = 2
Case 15 To 21
WeekOfMonth = 3
Case 22 To 28
WeekOfMonth = 4
Case 29 To 31
WeekOfMonth = 5
Case Else
WeekOfMonth = 0
End Select
End Function

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:lP************@news04.bloor.is.net.cable.roge rs.com...

Wayne thanks for the response. The answer to you question is: I would like 3 and 5 returned.
More examples: taking the month July/2004, if you pass in July 1, 2004 the function will return 1 because it is the first Thursday of the month, if you pass in July 8, 2004 the function will return 2 because it is the second Thursday of the month, if you pass in July 7, 2004 it will return 1 because it is the first Wednesday of the month, if you pass in July 29, 2004 the function will return 5 because it is the fifth Thursday of the Month.

Thanks
Barry

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Bb*******************@newssvr23.news.prodigy. com... Which are you wanting? You stated that the first day of the week is Sunday, following that logic, July 15, 2004 should return 2 and July 31, 2004 should return 4 since the first week in July starts on Sunday, July 4, 2004. Do you want the numbers above returned or the 3 and 5 you mentioned in your
message?

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:OA***************@news04.bloor.is.net.cable.r ogers.com...

Hi Programmers,

I am trying to figure out how to create a function what will return the Week of the Month when a date is past in.

Looking at the month of July for 2004. Sunday is the first day of the

week, Saturday the last day of the week.

Passing the date July 14, 2004 to the WeekOfMonth function will return a

2 because July 14, 2004 is the second Wednesday of the

Month.
Passing the date July 15, 2004 to the WeekOfMonth function will return a

3 because July 15, 2004 is the third Thursday of the Month.

Passing the date July 31, 2004 to the WeekOfMonth function will return a

5 because July 31, 2004 is the fifth Saturday of the Month.

Can anyone give me a clue of how to create this WeekOfMonth function?

Thanks,
Barry

[Wayne Morgan]

Actually, this is much shorter. It relies on the value of True being treated
as -1. "\" is integer division.

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
End Function

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:lP************@news04.bloor.is.net.cable.roge rs.com...

Wayne thanks for the response. The answer to you question is: I would like 3 and 5 returned.
More examples: taking the month July/2004, if you pass in July 1, 2004 the function will return 1 because it is the first Thursday of the month, if you pass in July 8, 2004 the function will return 2 because it is the second Thursday of the month, if you pass in July 7, 2004 it will return 1 because it is the first Wednesday of the month, if you pass in July 29, 2004 the function will return 5 because it is the fifth Thursday of the Month.

Thanks
Barry

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:Bb*******************@newssvr23.news.prodigy. com... Which are you wanting? You stated that the first day of the week is Sunday, following that logic, July 15, 2004 should return 2 and July 31, 2004 should return 4 since the first week in July starts on Sunday, July 4, 2004. Do you want the numbers above returned or the 3 and 5 you mentioned in your
message?

--
Wayne Morgan
MS Access MVP
"Barry Wright" <ba*****************@NOSPAMrogers.com> wrote in message
news:OA***************@news04.bloor.is.net.cable.r ogers.com...

Hi Programmers,

I am trying to figure out how to create a function what will return the Week of the Month when a date is past in.

Looking at the month of July for 2004. Sunday is the first day of the

week, Saturday the last day of the week.

Passing the date July 14, 2004 to the WeekOfMonth function will return a

2 because July 14, 2004 is the second Wednesday of the

Month.
Passing the date July 15, 2004 to the WeekOfMonth function will return a

3 because July 15, 2004 is the third Thursday of the Month.

Passing the date July 31, 2004 to the WeekOfMonth function will return a

5 because July 31, 2004 is the fifth Saturday of the Month.

Can anyone give me a clue of how to create this WeekOfMonth function?

Thanks,
Barry

[James Fortune]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Xt*************@newssvr24.news.prodigy.com>. ..

Actually, this is much shorter. It relies on the value of True being treated
as -1. "\" is integer division.

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
End Function

--
Wayne Morgan
MS Access MVP

That's a great solution Wayne. To get around the 'True' problem perhaps use:

WeekOfMonth = (intDate - 1) \ 7 + 1

James A. Fortune

[Barry Wright]

Thanks Guys,
Your soluntions worked superbly!
I also came up with a solution of my own but yours were 20% faster.

Regards
Barry Wright

Public Function WeekOfMonth(vDate As Date) As Long

'Barry Wright Solution
Dim vMonth As Long 'Month of Date Passed In
Dim vMonth1 As Long 'Calculated Month

'Initialize variables
vMonth = Month(vDate)
vMonth1 = Month(vDate)
WeekOfMonth = 0

Do While vMonth = vMonth1
vDate = DateAdd("d", -7, vDate)
vMonth1 = Month(vDate)
WeekOfMonth = WeekOfMonth + 1
Loop

End Function


"James Fortune" <ja******@oakland.edu> wrote in message news:a6**************************@posting.google.c om...
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:<Xt*************@newssvr24.news.prodigy.com>. ..

Actually, this is much shorter. It relies on the value of True being treated
as -1. "\" is integer division.

Public Function WeekOfMonth(dteInputDate As Date) As Integer
Dim intDate As Integer
intDate = Day(dteInputDate)
WeekOfMonth = (intDate \ 7) + 1 + (intDate Mod 7 = 0)
End Function

--
Wayne Morgan
MS Access MVP

That's a great solution Wayne. To get around the 'True' problem perhaps use:

WeekOfMonth = (intDate - 1) \ 7 + 1

James A. Fortune