Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    European 'WeekNm' (2003 (SP2))

    I've seen this issue before & not really bothered with fixing any spreadsheets till now. I did find this thread European weeknum & used the enclosed VBA successfully, or rather to an extent.

    The ISO 8601 states that weeks begin on a Monday, however the VBA has the week starting on a Sunday. Not much of an issue, if it wasn't for the fact that the data I'm working with is from machinery that runs 24/7.

    If I could understand the code better I could probably sort htis out myself, but I don't <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Here's the code

    Function WeekNum(aDate As Date) As Integer
    Dim datJan4 As Date
    Dim datWeek1 As Date
    Dim intYear As Integer
    Dim intDayOfWeek As Integer

    For intYear = Year(aDate) + 1 To Year(aDate) - 1 Step -1
    datJan4 = DateSerial(intYear, 1, 4)
    intDayOfWeek = Weekday(datJan4, vbUseSystemDayOfWeek)
    datWeek1 = datJan4 + 1 - intDayOfWeek
    If aDate >= datWeek1 Then
    Exit For
    End If
    Next intYear
    WeekNum = (aDate - datWeek1) 7 + 1
    End Function

  2. #2
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European 'WeekNm' (2003 (SP2))

    Ah, found an additional problem, whilst testing the function;

    2006 has the fix in place & working but for 2000 this is different, despite both years (apparently) having Jan 1st starting on a Sunday.

    Ive attache an example.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: European 'WeekNm' (2003 (SP2))

    1) You must put the function in a standard module, not in ThisWorkbook.

    2) Change vbUseSystem to vbMonday (I live in a country where the system settings have Monday as the first day of the week, so for me those are equivalent, but if your system considers Sunday to be the first day of the week, you must use vbMonday explicitly).

    You can also use the shorter function from <post:=635,063>post 635,063</post:>.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: European 'WeekNm' (2003 (SP2))

    Got it.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •