1. ## 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. ## 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
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
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. ## 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
•