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

    Isomonth (all versions)

    hello again,
    i
    Attached Files Attached Files

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

    Re: Isomonth (all versions)

    As far as I know, ISO 8601 doesn't have a special definition for the month - it simply uses the calendar month. See this Wikipedia article. So with a date in cell B2, the formula =MONTH(B2) will return the month number.

    If you know of a special definition of "ISO month", please post it, and if possible, provide a reference

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

    Re: Isomonth (all versions)

    hi hans,
    i found a solution, based on John Green - he gave the isostart of a week, so my former formula
    can be used. every week (the whole) belongs to a month. i didn
    Attached Files Attached Files

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

    Re: Isomonth (all versions)

    Here is another solution: a function ISOMonat that works the same way as ISOJahr:

    Function ISOMonat(aDate As Date) As Integer
    Dim datDay4 As Date
    Dim datWeek1 As Date
    Dim intYear As Integer
    Dim intMonth As Integer
    Dim intDayOfWeek As Integer
    intYear = Year(aDate)
    For intMonth = Month(aDate) + 1 To Month(aDate) - 1 Step -1
    datDay4 = DateSerial(intYear, intMonth, 4)
    intDayOfWeek = WeekDay(datDay4, vbMonday)
    datWeek1 = datDay4 + 1 - intDayOfWeek
    If aDate >= datWeek1 Then
    Exit For
    End If
    Next intMonth
    ISOMonat = intMonth
    End Function

    With a date in cell B2, you can use =ISOMonat(B2) to get the month number you want.

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

    Re: Isomonth (all versions)

    thanks hans,
    it works similar to my solution instead of calendarweek 22 which belongs on the whole to month 5 in your fomula - friday is june 1st.
    and 31.12.2007 where your fomula gives a 13th month....
    regards,
    stefan

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

    Re: Isomonth (all versions)

    The 13 is easily repaired: insert the lines

    If intMonth = 13 Then
    intMonth = 1
    ElseIf intMonth = 0 Then
    intMonth = 12
    End If

    above the line

    ISOMonat = intMonth

    If you want to keep in line with the ISO definition of the week of the year, I'd think that ISO week 22 in 2007 should belong to May, since most of that week falls within May. But since this is not an official ISO definition, it's a matter of personal preference.

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

    Re: Isomonth (all versions)

    Hi Hans,
    you are right, it is personal preference - anyway i found out a third solution using ionly formulas, who might be suitable for anyone:
    for all solutions their focus on the start of the week is most important -
    in c2: calendarweek, in e2: isoyear/year
    =DATE(E2;1;7*C2-3-WEEKDAY(DATE(E2;[img]/forums/images/smilies/wink.gif[/img];3)) -> getting out the monday of the week, and then ->
    =IF($C2=53;12;IF($C2=52;12;IF(MONTH(DATE(E2;1;7*C2-3-WEEKDAY(DATE(E2;[img]/forums/images/smilies/wink.gif[/img];3))+4)<MONTH(DATE(E2;1;7*C2-3-WEEKDAY(DATE(E2;[img]/forums/images/smilies/wink.gif[/img];3))+5);MONTH(DATE(E2;1;7*C2-3-WEEKDAY(DATE(E2;[img]/forums/images/smilies/wink.gif[/img];3))+4);MONTH(DATE(E2;1;7*C2-3-WEEKDAY(DATE(E2;[img]/forums/images/smilies/wink.gif[/img];3))+5))))
    this gets out for every day from monday to sunday the tangliable month, coresponding to friday (=last workday of a "normal" week)
    regards,
    stefan

Posting Permissions

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