![]() Put this function in a macromodule in the workbook. ![]() ![]() Instead of a worksheetformula you can use a VBA-formula in a function It is easy to 'count' the number of occurances of a given day of the week within an interval - but your explination does not give me sufficient info to know what to 'count', nuch less how to expresss it for your purposes. An optional argument is provided to this function, which is the first day of the week. 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) This function returns an integer value from 1 to 7 range. From a date i want to caluculate what week of the year it is i.e 7 apr 03 week 15 I cannot find a function to obtain this, Does anybody know of one, or. 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) We always ensure that the last week number is listed below. 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 Watch the current week number 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) In cell A3 the weekdaynumber of the desired day 1. In cell A2 the number of the desired week 1. Change the name of the textboxes and the sheet if needed. TextBox2 WeekNum (.TextBox1) End With End Sub Function WeekNum (D As Date) As Integer WeekNum CInt (Format (D, 'ww', 2)) End Function. 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 Put in a new Module the following: Code: Sub textboxes () With Sheets ('Sheet1').
0 Comments
Leave a Reply. |