Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Week Number using VBA in Excel
Hi Can you please help to get this script run in excel. as I am finding
difficulties in running. Public Function ISOWeekNum(AnyDate As Date, _ Optional WhichFormat As Variant) As Integer ' ' WhichFormat: missing or < 2 then returns week number, ' = 2 then YYWW ' Dim ThisYear As Integer Dim PreviousYearStart As Date Dim ThisYearStart As Date Dim NextYearStart As Date Dim YearNum As Integer ThisYear = Year(AnyDate) ThisYearStart = YearStart(ThisYear) PreviousYearStart = YearStart(ThisYear - 1) NextYearStart = YearStart(ThisYear + 1) Select Case AnyDate Case Is = NextYearStart ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1 YearNum = Year(AnyDate) + 1 Case Is < ThisYearStart ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1 YearNum = Year(AnyDate) - 1 Case Else ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1 YearNum = Year(AnyDate) End Select If IsMissing(WhichFormat) Then Exit Function End If If WhichFormat = 2 Then ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _ Format(ISOWeekNum, "00")) End If End Function |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
group sales by week and week number | Excel Discussion (Misc queries) | |||
Can Excel turn % hrs worked/week put in a cell into a real number | Excel Worksheet Functions | |||
How do I set up a week by week skill training schedule in excel? | Excel Discussion (Misc queries) | |||
how to get week number in month in excel ? | Charts and Charting in Excel | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) |