Results 1 to 3 of 3

Thread: Year (all)

  1. #1
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Year (all)

    hello,

    i know some formulas and codes to find out a iso weeknumber. I even found out to get out the connected month (
    // kw in J; Date in G // =IF(J3=53;12;IF(J3=52;12;IF(MONTH(G3+4)<MONTH(G3+5 );MONTH(G3+4);MONTH(G3+5))))
    - ( If you count in iso-weeks).
    but it seems to bee impossible to get out the iso-year of a date, e.g.: 31.12.2007 / Kw: 1 (from 2008). Is there any possibility for
    it?
    i tried to get a solution but couldn't find a way,
    stefan

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

    Re: Year (all)

    I use these custom VBA functions:

    Function ISOWeekNum(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
    ISOWeekNum = (aDate - datWeek1) 7 + 1
    End Function

    Function ISOYear(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
    ISOYear = intYear
    End Function

    If you copy them into as module in your workbook, you can use formulas such as

    =ISOWeekNum(A1)

    and

    =ISOYear(A1)

    If you place the functions in a module in your Personal.xls, use

    =Personal.xls!ISOWeekNum(A1)

    (If you're using the German language version of Excel, Personal.xls probably has a different name)

  3. #3
    2 Star Lounger
    Join Date
    Feb 2007
    Location
    Vienna, Wien, Austria
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Year (all)

    thanks again hans, it works as your last support.
    for dinkw, i use

    Function Kw(Datum As Date) As Integer
    Dim lT As Long
    lT = DateSerial(Year(Datum + (8 - WeekDay(Datum)) Mod 7 - 3), 1, 1)
    Kw = ((Datum - lT - 3 + (WeekDay(lT) + 1) Mod 7)) 7 + 1
    End Function

Posting Permissions

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