Get WeekNumber for a given date
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
Original Author: Jeff Mayes
Inputs
Date, passed as a string
Returns
Week number in calendar year, returned as an integer
Code
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
Loading Comments ...
Comments
No comments have been added for this post.
You must be logged in to make a comment.