Re: Week Number using VBA in Excel

On Sun, 5 Aug 2007 16:55:56 +0200, "Hans Terkelsen" <dk> wrote:


"Ron Rosenfeld" <ronrosenfeld@xxxxxxxxxx> wrote in message news:gsjbb3demali08n719c21e3982rn1gf390@xxxxxxxxxx
On Sun, 5 Aug 2007 13:32:59 +0200, "Hans Terkelsen" <dk> wrote:


"Ron Rosenfeld" <ronrosenfeld@xxxxxxxxxx> wrote in message news:ste9b3puqocjp3hbo69ddfgvpk9fnfbjeq@xxxxxxxxxx
On Sat, 4 Aug 2007 08:00:01 -0700, Kam <Kam@xxxxxxxxxxxxxxxxxxxxxxxxx> 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

Ron,
if you will accept my testresult, your function is true for all (VB) dates
4 jan 100 - 24 dec 9999!
And still clear (enough) :-)
You go back 1 day except on mondays.
I also looked for a simple modification,
but could not come up with anything better.
OK and thanks, Hans.


Thank you, Hans.

I wonder if MS will ever correct these bugs. They've been around for a long
time.
--ron
.

Relevant Pages

  • Re: Week Number using VBA in Excel
    ... Optional WhichFormat As Variant) As Integer ... Dim PreviousYearStart As Date ... Dim ThisYearStart As Date ... If ISOWeeknum> 52 Then ...
    (microsoft.public.excel.misc)
  • Re: Week Number using VBA in Excel
    ... Optional WhichFormat As Variant) As Integer ... Dim PreviousYearStart As Date ... Dim ThisYearStart As Date ... If ISOWeeknum> 52 Then ...
    (microsoft.public.excel.misc)
  • Re: Week Number using VBA in Excel
    ... >Dim PreviousYearStart As Date ... >Dim ThisYearStart As Date ... If ISOWeeknum> 52 Then ... Your ISOWeeknumhas the advantage of sidestepping the bug in Datepart in a clear way. ...
    (microsoft.public.excel.misc)
  • Re: Week Number using VBA in Excel
    ... Optional WhichFormat As Variant) As Integer ... Dim PreviousYearStart As Date ... Dim ThisYearStart As Date ... If ISOWeeknum> 52 Then ...
    (microsoft.public.excel.misc)
  • Re: Bug or by Design?
    ... Sub SetCCText ... Dim CC As ContentControl, loopCC As ContentControl ... If not a bug then a serious design flaw IMHO. ...
    (microsoft.public.word.vba.general)
Loading