VBA‎ > ‎

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
 
 
Comments