hello again,
i

2. ## 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. ## 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

4. ## 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
For intMonth = Month(aDate) + 1 To Month(aDate) - 1 Step -1
datDay4 = DateSerial(intYear, intMonth, 4)
intDayOfWeek = WeekDay(datDay4, vbMonday)
datWeek1 = datDay4 + 1 - intDayOfWeek
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. ## 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. ## 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. ## 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
•