ISOWEEKNUMBER - Returning a non-existing week 53.

[HikC]

Hi,

What did I overlook since the ISOweeknumber function returns week 53 for the date 12/30/2019?

Public Function ISOweeknumber(Dt As Date) As Long
    ISOweeknumber = DatePart("ww", Dt, vbMonday, vbFirstFourDays)
End Function

Sub FindWeekNumber()

Dim Dt1 As Date, Dt2 As Date, Dt3 As Date
Dim We1 As Long, We2 As Long, We3 As Long

Dt1 = "12/29/2019"
Dt2 = "12/30/2019"
Dt3 = "12/31/2019"

We1 = ISOweeknumber(Dt1)    '   =52
We2 = ISOweeknumber(Dt2)    '   =53 ???
We3 = ISOweeknumber(Dt3)    '   =1

MsgBox We1 & " " & We2 & " " & We3

End Sub

Best regards
Henrik

 

[mole999]

ISO Week Number
This method uses the International Standards Organization (ISO) definition of a week number. An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday. Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.
Note that it is possible that the first few days of a year may fall into week 52 or week 53. Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday, and second, that the first week must contain a Thursday. For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010), the week doesn't contains Thursday (or, said another way, the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010. The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009. Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010, which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).

From

> http://www.cpearson.com/excel/WeekNumbers.aspx

 

[HikC]

Mole999,

Thank you for your reply. But still, the function should not return "53", since there is no week 53 at the end of 2019. And according to the returned values week 53 should be a "one-day week", since 12/29/2019 (Sunday) belongs to week 52, 12/30/2019 (Monday) to week 53 - apparently - and 12/31/2019 (Tuesday) to week 1. Furthermore, the built-in Excel function (i.e. not VBA) ISOWEEKNUMBER correctly assigns 12/30/2019 to week 1.

/Henrik

 

[MARK858]

Public Function ISOweeknum(ByVal v_Date As Date) As Integer

ISOweeknum = DatePart("ww", v_Date - Weekday(v_Date, 2) + 4, 2, 2)
End Function

Sub FindWeekNumber()

Dim Dt1 As Date, Dt2 As Date, Dt3 As Date
Dim We1 As Long, We2 As Long, We3 As Long

Dt1 = "12/29/2019"
Dt2 = "12/30/2019"
Dt3 = "12/31/2019"

We1 = ISOweeknum(Dt1)    '   =52
We2 = ISOweeknum(Dt2)   '   =53 ???
We3 = ISOweeknum(Dt3) '   =1

MsgBox We1 & " " & We2 & " " & We3

End Sub

Function courtesy of snb.

 

[HikC]

Thank you, Mark858.

/Henrik

 

[steve the fish]

This produces 1 on my excel 2016 machine.

=ISOWEEKNUM(DATE(2019,12,30))

 

[MARK858]

Same here Steve but couldn't get it to work as a worksheetfunction in VBA at least with strings as the OP has it.

 

[steve the fish]

That weird because this produces 1 for me too:

myDate = DateSerial(2019, 12, 30)
MsgBox WorksheetFunction.IsoWeekNum(myDate)

 

[MARK858]

As I stated with Date strings as per the OP's post. Can get it to work with DateSerial or Clng.

 

[steve the fish]

Its going to depend what date system the machine uses i believe. Consider the below:

Dim Dt1 As Date, Dt2 As Date, Dt3 As Date, Dt4 As Date

Dt1 = #12/29/2019#
Dt2 = "12/29/2019"

Dt3 = #12/10/2019#
Dt4 = "12/10/2019"

MsgBox Dt1 = Dt2
MsgBox Dt3 = Dt4

Its the date which can be ambiguous that causes the problem. I personally would never give VBA the chance to get it wrong ie use DateSerial for example.