Week of Month?

[Guest]

I need to find out the week of the month.
Data:
1/18/2004 20.00
1/25/2004 20.00
2/1/2004 40.00
2/29/2004 20.00
3/1/2004 20.00
4/4/2204 80.00
4/11/2004 20.00
4/18/2004 20.00
Here's what I want to report:

Month Week 1 Week 2 Week 3 Week 4 Week 5
Jan. 0.00 0.00 20.00 20.00
Feb. 40.00 0.00 0.00 0.00 20.00
Mar. 20.00 0.00 0.00 0.00
Apr. 80.00 20.00 20.00 0.00

How do I find out the week as per the month?
Thanks, Tammy

 

[Christian]

Hi Tammy,

Have a look at this post by Eric Redleaf http://tinyurl.com/4juhj

Copy the function below to a module. I'm not at all expert in this but I
believe that you should state it as a public function like:
Public Function weekofmonth(theday As Date) As Byte
Dim i As Byte
Dim firstofmonth As Date
Dim firstfriday As Byte


i = 0
firstofmonth = DateSerial(Year(theday), Month(theday), 1)
' find the date of the first Friday
Do Until (Weekday(firstofmonth + i) = 6)
i = i + 1
Loop
firstfriday = Day(firstofmonth + i)
' now that we know the date of the first Friday we can
'calc week mathematically.
If Day(theday) <= firstfriday Then
weekofmonth = 1
Else
weekofmonth = ((Day(theday) - (firstfriday + 1)) \ 7) + 2

End If

End Function



In your query you can now create a calculated field like:
WoM: weekofmonth([Date])