On Sun, 5 Aug 2007 13:32:59 +0200, "Hans Terkelsen" <dk wrote:


"Ron Rosenfeld" wrote in message ...
On Sat, 4 Aug 2007 08:00:01 -0700, Kam wrote:

Hi Can you please help to get this script run in excel. as I am finding
difficulties in running.

Public Function ISOWeekNum(AnyDate As Date, _
Optional WhichFormat As Variant) As Integer
'
' WhichFormat: missing or < 2 then returns week number,
' = 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer

ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is = NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate - PreviousYearStart) \ 7 + 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select

If IsMissing(WhichFormat) Then
Exit Function
End If
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))
End If

End Function



What problems are you having?

There are simpler algorithms.

For example, to just get the ISOWeeknumber:

==================================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt, vbMonday, vbFirstFourDays)
If ISOWeeknum 52 Then
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 Then
ISOWeeknum = 1
End If
End If
End Function
====================================
--ron

Hi Ron!

Your ISOWeeknum(dt As Date) has the advantage of sidestepping the bug in Datepart in a clear way.
But some of the errors remain.
Sun 2 jan 2101 still gives week 53, which should be 52 like Sat 1 jan 2101.
A bit into the future, I'll admit.

Staying with Datepart there is this:

Function ISOWknum(d As Date) '25/12/1899-26/12/9999
ISOWknum = DatePart("ww", ((d + 5) \ 7) * 7, 2, 2)
End Function

It looks at the Saturday of the same week.

Otherwise I'm quite proud of this

Function WkIso(d) '..1/1/100-31/12/9999..
WkIso = ((((d + 692501) \ 7 Mod 20871) * 28 + 4383) Mod 146096 Mod 1461) \ 28 + 1
End Function

It is very fast, doesn't need datefunctions.

Hans.



Very good Hans!

I was not aware of that BUG in DatePart. (And, apparently, neither was
Microsoft, as the only BUG they mention is the one for Mondays).

I like both of your workarounds. The one I came up with is not as fast as
yours, but it does clarify the bugs, I think. I haven't tested it yet, though:

========================================
Function ISOWeeknum(dt As Date) As Integer
ISOWeeknum = DatePart("ww", dt + (Weekday(dt, vbMonday) < 1), _
vbMonday, vbFirstFourDays)
If DatePart("ww", dt + 7, vbMonday, vbFirstFourDays) = 2 _
Then ISOWeeknum = 1
End Function
===========================================
--ron