Save file with ISO correct Workweek

Hello Everyone,

Last year this portion of my macro worked great assigning the current work week according to ISO standards.

ActiveWorkbook.SaveAs Application.UserName & "_" & "FCST" & "_" & Format(Date, "MMDDYYYY") & "_" & "CW" & Format(Date, "WW", vbUseSystemDayOfWeek, vbUseSystem)

Since the change of the year it does not work. For instance, today I ran it and it says we are in work week 3, but ISO says we are in work week 2. I don't know how to fix and I can't seem to find the answer.

Thoughts?

Thanks,
Matt

Hello Matt,

Since the ISO 8601 week and the Gregorian calendar week are not identical, you will need to use a conversion function to return the correct ISO week.

From Chip Pearson's site http://www.cpearson.com/excel/WeekNumbers.aspx

Public Function IsoWeekNumber(InDate As Date) As Integer
    Dim D As Long
    D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3)
    IsoWeekNumber = Int((InDate - D + Weekday(D) + 5) / 7)
End Function

Hello Matt,

Copy Chip's code in a standard VBA Module. Add this line where you need it...

ActiveWorkbook.SaveAs Application.UserName & "_" & "FCST" & "_" & Format(Date, "MMDDYYYY") & "_" & "CW" & IsoWeekNumber(Date)