![]() ![]() Put this function in a macromodule in the workbook. TextBox2 WeekNum (.TextBox1) End With End Sub Function WeekNum (D As Date) As Integer WeekNum CInt (Format (D, 'ww', 2)) End Function Change the name of the textboxes and the sheet if needed. Start with the first week that has at least four days in the new year. Instead of a worksheetformula you can use a VBA-formula in a function Start with week in which January 1 occurs (default). 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),0)<ģ.3 VBA-Function as a User Defined Function (UDF) There is a lot of flexibility using the VBA cell function, as you can reference a cell using a number for column and cell, especially with scripts that can loop. 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) In this article, we will use Excel VBA to find the week number. in the ISO-system monday is the first day of the week Week Number refers to the first complete week of the calendar year, which is Week No.1 and continues in numerical order until Week No. In the ISO-system the 4th of january is always in week 1 =DATE(A1,1,4) ![]() You would have to get out your calendar and count the. This function is as per the ISO standards, which says the week. the week number This can be confusing because you probably wouldn't know what Week 15 actually is. In cell A3 the weekdaynumber of the desired day 1. ISOWEEKNUM function allows you to find out the ISO week number of the week for a particular date. In cell A2 the number of the desired week 1. The week number depends if the first week of the year belongs in the previous year or new year. 7*( 40-1)+Dateserial( 2011,1,4)- Weekday(DateSerial( 2011, 1, 4), 2)+ 3ģ.2 Worksheetfunction: calculate a certain date in a certain (ISO) week ![]()
0 Comments
Leave a Reply. |