To use the code below:
Week(#12/10/2010#)
This will return a 50, indicating that 12/10/2010 falls within the 50th week of the year.
NOTE: weeks are assumed to start on a Monday with this code and end on Sunday.
To change it to have the week end on Saturday, replace the "vbSunday" with "vbSaturday".
CODE:
Function WEEK(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
WEEK = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
B = Year(InputDate + ((8 - A) Mod 7) - 3)
C = DateSerial(B, 1, 1)
D = (Weekday(C, vbSunday) + 1) Mod 7
WEEK = Int((InputDate - C - 3 + D) / 7) + 1
End Function
If you then want to know the day of the week that each week starts with, use this code:
Function WEEKStart(InputDate As Long) As Date
Dim A As Integer, B As Integer, C As Long, D As Integer
WEEKStart = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
If A = 1 Then
B = 6
ElseIf A = 2 Then
B = 0
ElseIf A = 3 Then
B = 1
ElseIf A = 4 Then
B = 2
ElseIf A = 5 Then
B = 3
ElseIf A = 6 Then
B = 4
ElseIf A = 7 Then
B = 5
End If
WEEKStart = InputDate - B
End Function