Weeknumbers in Excel as they should be
hhalle wrote on December 23, 2005 04:25 EST
Excel as well as many other product always consider january 1 of each year as week nr 1.
This is not correct.
I wrote the following function WkNr() to return the corect weeknumber.
The rule as is stated in The Netherlands is the following:
When January 1 falls on or after Thursday Week 1 is the next Monday
When January 1 falls on or before Wednesday the previous Monday is week 1
Public Function WkNr(wdate As Date) As Date
Dim wk As Integer
Dim wd As Integer
Dim yr As Integer
Dim tDate, ttdate As Date
yr = Year(wdate)
tDate = DateValue("01-01-" & yr)
ttdate = tDate
wd = Weekday(tDate)
Select Case wd
Case 1
tDate = tDate + 1
Case 2
tDate = tDate + 0
Case 3
tDate = tDate - 1
Case 4
tDate = tDate - 2
Case 5
tDate = tDate + 4
Case 6
tDate = tDate + 3
Case 7
tDate = tDate + 2
End Select
If Int((wdate - tDate) / 7) + 1 > 0 Then
If ((wdate = DateValue("30-12-" & yr) And Int((wdate - tDate) / 7) + 1 = 53 And (Weekday(DateValue("30-12-" & yr)) = 2)) Or (wdate = DateValue("31-12-" & yr) And Int((wdate - tDate) / 7) + 1 = 53 And (Weekday(DateValue("31-12-" & yr)) = 2 Or Weekday(DateValue("31-12-" & yr)) = 3))) Then
WkNr = 1
Else
WkNr = Int((wdate - tDate) / 7) + 1
End If
Else
WkNr = WkNr(wdate - Day(wdate))
End If
End Function
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
Keys to Reading an Annual Report (Barron's Business Keys)
