ISO weeknumber
| Suggestions |
Example file
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
Ergo - the 1st of january is in week 1 if it's a monday, tuesday, wednesday or thursday
VBA comprises 2 functions that deliver the weeknumber of a date: DatePart and Format.- the 4th of january is always in week 1 of any year - the first thursday in a year is always in week 1 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) 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) 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) sunday=Date-WeekDay(Date,2) thurday=Date-WeekDay(Date,2)+4 ISOweeknumber=Datepart("ww",Date-WeekDay(Date,2)+4,2,2) ISOweeknumber=Format("ww",Date-WeekDay(Date,2)+4,2,2)
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 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)
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) - 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) 7*(v_week-1)+Dateserial(v_year,1,4)- Weekday(DateSerial(v_year, 1, 4), 2) 7*(v_week-1)+Dateserial(v_year,1,4)- Weekday(DateSerial(v_year, 1, 4), 2)+v_day 7*(40-1)+Dateserial(2011,1,4)- Weekday(DateSerial(2011, 1, 4), 2)+3
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)<
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=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= 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 FunctionFunction 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= ISOday(2012,22,"friday") |
1 The ISO weeknumber