ElementB
2018-11-26
weekNum
Well display the week of the year.
The function takes two parameters, the first is a date field in which the week number is calculated.
The second parameter is the start of the week:
1 - Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thursday
6 - Friday
7 - Saturday
Numeric
Option Explicit
Function weekNum(p1 As Date,p2 As Double) As Double
Dim dayOfWeek As Integer
Dim weekOfYear As Integer
Dim monthOfYear As Integer
dim dayOfYear as integer
weekOfYear = Format(p1, "ww")
dayOfWeek = Format(p1, "w")
monthOfYear = Format(p1, "m")
dayOfYear = Format(p1, "d")
p2 = Int(p2) 'get rid of any decimals that might be added
If p2 < 1 Or p2 > 7 Then p2 = 1 'if it is outside the limits then default to 1
If p2 = 1 Then
weeknum = weekOfYear
ElseIf p2 = 2 Then
If monthOfYear = 1 And dayOfYear = 1 Then
weeknum = 1
ElseIf dayOfWeek < p2 Then
weeknum = weekOfYear - 1
Else
weeknum = weekOfYear
End If
Else 'If p2 = 3 Then
If monthOfYear = 1 And dayOfYear = 1 Then
weeknum = 1
ElseIf dayOfWeek >= p2 Then
weeknum = weekOfYear + 1
Else
weeknum = weekOfYear
End If
End If
End Function
Date
p1
Date field
Numeric
p2
Start of week