Example file
ISO weeknumber

1 The ISO weeknumber

The ISO-criteria for the first week in a year:
- a week starts at monday; so monday is the first day of any week
- the first week in a year must contain at least 4 days in that year

ISOweeknumber1111
monday1
tuesday21
wednesday321
thursday4321
friday54321
saturday654321
sunday7654321

Ergo
- the 1st of january is in week 1 if it's a monday, tuesday, wednesday or thursday
- the 4th of january is always in week 1 of any year
- the first thursday in a year is always in week 1

2.1 VBA and ISO-weeknumber

VBA comprises 2 functions that deliver the weeknumber of a date: DatePart and Format.
DatePart results in a number, Format retuns a string.
Here we discuss the function Datepart.

To retrieve the weeknumber using DatePart:
weeknumber=DatePart("ww",Date)
Het result is the American weeknumber (first weekday: sunday; first week in a year: the week that contains 1 january)

Adapt DatePart to the ISO-criteria
You can add an argument to the function Datepart to indicate which day to consider the first weekday. In the ISO-case vbMonday.
You can also add an argument to the function Datepart to indicate which is the first week of any year. In the ISO-case vbFirstFourdays.
You can use either the arguments' names or their indexnumber
ISOweeknumber=Datepart("ww",Date,vbMonday,vbFirstFourDays)
ISOweeknumber=Datepart("ww",Date,2,2)
Both functions (DatePart and Format) contain a (documented) bug:
Every 400 year the result of a first sunday/monday of a year will be incorrect because of the miscalculation of leap-years in 400 year.
You can circumvent this bug if you calculate the weeknumber of the thursday in the same week as the date you want it's weeknumber to be calculated.

How to get the thursday in the same week:
The function WeekDay returns the number of every day: monday=1, tuesday=2, wednesday=3, ... etc.
This number is dependent of the criterion for the first day of the week; analogous to DatePart we use the argument vbMonday.
daynumber=WeekDay(Date,vbMonday)
daynumber=WeekDay(Date,2)
Subtract from a date it's daynumber; the result: the sunday previous to the date.
sunday=Date-WeekDay(Date,2)
Add 4 to that sunday; the result: the thursday in that week.
thurday=Date-WeekDay(Date,2)+4
The ISO-weeknumber of the thursday in the same week of the date you need it's weeknumber
ISOweeknumber=Datepart("ww",Date-WeekDay(Date,2)+4,2,2)
The ISO-weeknumber using the function Format
ISOweeknumber=Format("ww",Date-WeekDay(Date,2)+4,2,2)

2.2 ISO-weeknumber: a worksheetformula

According to the ISO criteria the first thursday of any year is always in week 1.

You can use this to calculate the weeknumber
how many weeks have passed since the first thursday in a year until the thursday in the week that contains the date which's weeknumber you want to get.

Assuming that date is in cell A1

- the thursday in the week of the date in A1
A1-WEEKDAY(A1,2)+4
- the year of this thursday
YEAR(A1-WEEKDAY(A1,2)+4)
- the first of january of the year of this thursday
DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,1)
- the number of days between this thursday and the first of january in the same year
A1-WEEKDAY(A1,2)+4-DATUM(YEAR(A1-WEEKDAY(A1,2)+4),1,1)
- the number of weeks: divide the number of days by 7 and round the result
=TRUNC(....)/7)
- add 1 to prevent the first week to have the number 0
=(....) +1
Result
=TRUNC((A1-WEEKDAY(A1,2)+4-DATE(YEAR(A1-WEEKDAY(A1,2)+4),1,1))/7)+1
You will get the same result using a formula that already contains the last correction (+1)
This formula calculates the amount of weeks between the first thursday of the year and the thursday in the week next to the date
=TRUNC((A1-WEEKDAY(A1,2)+11-DATE(YEAR(A1-WEEKDAY(A1,2)+4);1;1))/7)

3.1 VBA: calculate a certain day in a certain ISO-week

E.g. if you want to calculate the wednesday in week 40

The calculation steps
Determine the last sunday of the previous year
Add to that 7 times the number of weeks minus 1
Add to that the weekdaynumber of the day you want to be the result.

Assign the year to the variable v_year: 4 digits
Assign the weeknumber to the variable v_week: 1...53
Assign the desired day to the variable v_day (monday=1, tuesday=2, wednesday=3 etc. )


- In the ISO-system the 4th of january is always in week 1
Dateserial(v_year,1,4)
- in the ISO-system monday is the first day of the week
- subtract from the 4th of januari it's weekdaynumber; resulting in: the sunday preceding the 4th of january
DateSerial(v_year,1,4)- Weekday(DateSerial(v_year, 1, 4), 2)
- add 7 times the number of weeks minus 1
7*(v_week-1)+Dateserial(v_year,1,4)- Weekday(DateSerial(v_year, 1, 4), 2)
- add the weekdaynumber of the date you want to retrieve
7*(v_week-1)+Dateserial(v_year,1,4)- Weekday(DateSerial(v_year, 1, 4), 2)+v_day
'hardcoded' for the wednesday in week 40 in 2011:
7*(40-1)+Dateserial(2011,1,4)- Weekday(DateSerial(2011, 1, 4), 2)+3

3.2 Worksheetfunction: calculate a certain date in a certain (ISO) week

In cell A1 the year 4 digits
In cell A2 the number of the desired week 1 ... 52
In cell A3 the weekdaynumber of the desired day1 ... 7

- In the ISO-system the 4th of january is always in week 1
=DATE(A1,1,4)
- in the ISO-system monday is the first day of the week
- subtract from the 4th of januari it's weekdaynumber; resulting in: the sunday preceding the 4th of january
=DATE(A1,1,4)-WEEKDAY(DATE(A1,1,4),2)
- add 7 times the number of weeks minus 1
=7*(A2-1)+DATE(A1,1,4)-WEEKDAY(DATE(A1,1,4),2)
- add the weekdaynumber of the date you want to retrieve (mo=1, tue=2, wed=3, etc.)
=7*(A2-1)+DATE(A1,1,4)-WEEKDAY(DATE(A1,1,4),2)+A3

If you want to give the user the opportunity to indicate the dayname instead of the weekdaynumber you can use:
=7*(A2-1)+DATE(A1,1,4)-WEEKDAY(DATE(A1,1,4),2)+match(left(A3,2),{"mo","tu","we","th","fr","sa","su"},0)<

3.3 VBA-Function as a User Defined Function (UDF)

Instead of a worksheetformula you can use a VBA-formula in a function
Put this function in a macromodule in the workbook.

A UDF to determine the ISO-weeknumber:
Public Function ISOweeknum(ByVal v_Date As Date) As Integer
ISOweeknum = DatePart("ww", v_Date - Weekday(v_Date, 2)+ 4, 2, 2)
End Function
to calulate the weeknumber of 12-08-2012
=ISOweeknum("12-08-2012")

Assuming the date in cell A4, enter the formula in a cell this way
=ISOweeknum(A4)

A UDF to determine a certain weekday in a defined ISO-week
Function ISOday(v_year As Integer, v_week As Integer, v_day As Integer) As Long
ISOday = 7 * (v_week - 1) + DateSerial(v_year, 1, 4) - Weekday(DateSerial(v_year, 1, 4), 2) + v_day
End Function
to return saturday in week 23 in 2011
= ISOday(2011,23,6)

Assuming in cells A1, A2 and A3 respectively year, weeknumber and weekdaynumber, enter the formula this way
= ISOday(A1,A2,A3)

If you want the user to enter the weekdayname instead of the weekdaynumber you can use
In this case v_day is the fullname 'monday' or the abbreviation 'mo', 'tuesday' or 'tu', etc.
Function ISOday(v_year As Integer, v_week As Integer, v_day As String) As Long
ISOday = 7*(v_week - 1)+DateSerial(v_year,1,4)- Weekday(DateSerial(v_year,1,4),2)+Application.Match(left(v_day,2), Array("mo","tu","we","th","fr","sa","su"),0)
End Function
or using one of Excel's builtin customlists
Function ISOday(v_year As Integer, v_week As Integer, v_day As String) As Long
ISOday = 7 *(v_week-1)+DateSerial(v_year,1,4) - Weekday(DateSerial(v_year, 1, 4), 2) + Application.Match(left(v_day,2),Application.GetCustomListContents(1), 0)
End Function
The formula in the worksheet for friday in week 22 in 2012
= ISOday(2012,22,"friday")