VBA code to determine the week number during the year using a date

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