Weeknumber Problem - John Spencer

M

Mike Collard

John

A member form another forum checked your formula, and
found it doesn't seem to work in all cases, eg: year 2004,
week 1 and week 2 returns wrong result.

His solution follows:

You can add the below code for DateFromWeekNum() in a
module in your access db. Then you can use the function
directly in queries. It returns the first day of the week
(as a date) given a week number and a year.

'---- just as an example of use ------
Sub test()
MsgBox DateFromWeekNum(3, 2004)
End Sub
'--------------------------------------------
Public Function DateFromWeekNum(w As Long, yr As Long) As
Date
'w is week number
'yr is year (4 digits, eg:1998)
Dim firstOfTheYear As Date
Dim daysInFirstWeek As Long

firstOfTheYear = DateSerial(yr, 1, 1)
daysInFirstWeek = 8 - Weekday(firstOfTheYear, 1)

DateFromWeekNum = (firstOfTheYear - 1) + IIf(w = 1, 1,
daysInFirstWeek + (w - 2) * 7 + 1)
End Function


Regards

Mike Collard
 
Top