Search code examples
vbadatems-accessiso8601

Calculate total of weeks in a specific year - ISO 8601 - VBA Access

Saw this question a few more times but can't seem to get it fixed in my VBA code.

I need to calculate the total number of weeks in a given year comform ISO 8601.

When I use the datediff function: iNumWeeks = DateDiff("ww", "1/1/2015", "31/12/2015", vbMonday, vbFirstJan1) it returns 52 while there are 53 weeks in 2015 (ISO 8601)

How can I get this done?

Solution

  • To quote from https://en.wikipedia.org/wiki/ISO_8601#Week_dates

    28 December is always in the last week of its year.

    Which means it's really as simple as

    Public Function WeeksInYear(lYear As Long) As Long
    
    WeeksInYear = DatePart("ww", DateSerial(lYear, 12, 28), vbMonday, vbFirstFourDays)

End Function