ISO week number in VBScript or VBA

Question

How can I get the ISO week number of some date in VBScript or VBA?

Michel de Ruiter · Accepted Answer · 2022-05-17T15:31:06.463

3

First, note that:

It is important to report the week year along with the week number, as the date's year could be different.
Several Windows components contain a bug for some years' last Monday.

In my experience the simplest, clearest and most robust way to compute this is:

Sub WeekNum(someDate, isoWeekYear, isoWeekNumber, isoWeekDay)
  Dim nearestThursday
  isoWeekDay = WeekDay(someDate, vbMonday)
  nearestThursday = DateAdd("d", 4 - Int(isoWeekDay), someDate)
  isoWeekYear = Year(nearestThursday)
  isoWeekNumber = Int((nearestThursday - DateSerial(isoWeekYear, 1, 1)) / 7) + 1
End Sub

This also returns the ISO day of the week, counting from 1 for Mondays.

edited May 17 '22 at 15:31

answered Jan 26 '16 at 09:39

Michel de Ruiter

7,131
5
49
74

I guess this was meant as a self-answer question, that's fine SO encourages them but please please improve the question it needs a bit of padding and ideally should show some attempt at the problem *(even though you obviously have a solution already)*. – user692942 Jan 26 '16 at 13:40
@Lankymart I cannot imagine which changes would improve the question. IMHO it is clear, specific, non-trivial and useful as is. As I did not find the answer to this question anywhere else I thought I should share this 'canonical' answer. – Michel de Ruiter Jan 26 '16 at 22:37
Fair enough, just usually a question like that would be deemed low quality as defined in [ask] – user692942 Jan 26 '16 at 23:04

score 0 · Answer 2 · answered Jan 26 '16 at 09:59

0

Enter any date into A1 cell, then run following code...

Range("A2").FormulaR1C1 = "=INT((R1C-DATE(YEAR(R1C-WEEKDAY(R1C-1)+4),1,3)+WEEKDAY(DATE(YEAR(R1C-WEEKDAY(R1C-1)+4),1,3))+5)/7)"

answered Jan 26 '16 at 09:59

Herry Markowitz

208
3
15

Great for Excel! But I was thinking of Access and Word as well. – Michel de Ruiter Jan 26 '16 at 10:03
1

Where is Excel mentioned in the question? Admittedly it's a poor question but still. – user692942 Jan 26 '16 at 13:38
This is the code to get the current week: iThisWeek = DatePart("ww", Date) _ Exchange "Date" by your cell or variable of which you want to know the week of. – Kathara Jan 26 '16 at 13:48
@Lankymart Herry took VBA as a hint for Excel. – Michel de Ruiter Jan 27 '16 at 15:16

Mik · Answer 3 · 2022-05-17T19:50:50.393

0

You can get it via DatePart() VBA function:

    Sub IsoWeek()
    Dim isoWeekNum As Byte
    Dim myDate As Date
        myDate = DateValue("01-01-2022")
        isoWeekNum = DatePart("ww", myDate, vbMonday, vbFirstFourDays)
        If isoWeekNum > 52 Then ' Bug check (to avoid the bug with Mondays)
            If Format(myDate + 7, "ww", vbMonday, vbFirstFourDays) = 2 Then isoWeekNum = 1
        End If
        Debug.Print isoWeekNum
    End Sub

edited May 17 '22 at 19:50

answered May 17 '22 at 12:51

Mik

2,099
1
5
19

1

Indeed! But did you read the [warning in the docs](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/datepart-function)? – Michel de Ruiter May 17 '22 at 15:29
Huge thank. I added a layer to avoid the bug. [docs.microsoft](https://learn.microsoft.com/en-us/office/troubleshoot/access/functions-return-wrong-week-number) – Mik May 17 '22 at 19:57

ISO week number in VBScript or VBA

3 Answers3

Linked