by Jeff Mayes (1 Submission)
Category: Math/Dates
Difficulty: Beginner
Date Added: Wed 3rd February 2021
Rating: (3 Votes)
This snippet will allow you to pass a string as a date, and get the week number for that calendar year, returned as an integer
Inputs
Date, passed as a string
Code Returns
Week number in calendar year, returned as an integer
Public Function WeekNum(sDate As String) As Integer
Dim iYear As Integer
Dim iMon As Integer
Dim sTemp As String
Dim iDay11 As Integer
Dim iDiff As Integer
If IsDate(sDate) And sDate <> "12:00:00 PM" Then
'determine the year
iYear = Year(sDate)
'determine the month
iMon = Month(sDate)
'determine weekday of Jan 1st
sTemp = "1/1/" & Trim(Str$(iYear))
iDay11 = Weekday(sTemp)
'now calculate the difference in days
iDiff = DateDiff("d", sTemp, sDate)
'base week
WeekNum = Int(iDiff / 7) + 1
'check for rollover based on day of week
If Weekday(sDate) < iDay11 Then
WeekNum = WeekNum + 1
End If
End If
End Function