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

 

[MARK858]

The function by snb produces the correct week number with the string layout as per post number 1 for the OP who is in mm/dd/yyyy format and for me and my system is in in dd/mm/yyyy format. It is just the string in the VBA needs to be in US (mm/dd/yyyy) format as is normal in VBA.

Agree with you about being much safer to use dateserial but wanted to show the OP how to get the function to work as the OP posted...

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

 

[steve the fish]

On your machine does it get "12/10/2019" wrong?

 

[MARK858]

Whoa, you are right. It doesn't

DateSerial or Clng it is then. Probably ok for the OP as in mm/dd/yyyy

Thanks Steve.

 

[steve the fish]

Its because its ambiguous. Perfectly acceptable dates in the UK and the US but they are different dates to each country. Try this #12/10/2019#

 

[MARK858]

Yep, that is better with #12/10/2019# but still prefer DateSerial

Knew the dates are ambiguous but normally VBA only accepts the US format so even more pain with dates

 

[HikC]

I'm not sure whether I contribute to clarification or confusion ... but I still need the ISOWeeknumber function provided by Mark858 (snb).

Using DATESERIAL (i.e. DATESERIAL(2019, 12, 30)) with my original ISOWeeknumber function also returns week 53 for 12/30/2019.

/Henrik

 

[MARK858]

Sorry HikC but can't test it at the moment as only on my laptop now which is Excel 2010 which doesn't support WorksheetFunction.ISOweeknum (which was probably my issue yesterday with it).

 

[HikC]

No problem. I'm happy with the solution you provided. Thanks.

/Henrik