Hello.

I posted this yesterday in the
microsoft.public.excel.programming newsgroup but didn't
get any response so I'll try this one instead since it
might be more suitable! :)

Also, I've searched through the bug center with no luck
and also tried to find some place where you can submit
new bugs but without finding anything. To the problem:

I recently discovered something that must be a bug when
using the DatePart-function in VBA and VBScript (probably
also in VB). This also occurs when doing a similar thing
with the Format-function in VBA.

I want to get the weeknumber of a certain date. I live in
Sweden and our week starts with monday and the first week
of the year is the one with first four days of January.
This means I should use vbMonday and vbFirstFourDays. We
also write dates like this: 2002-11-26.

So to an easy example of this (and it doesn't matter if
the date has been sent in string or date string format):

Sub DateTest()
    MsgBox DatePart("ww", "2003-12-29", vbMonday,
vbFirstFourDays)
End Sub

If you try this out you will get the messagebox to
display "53". When looking this up in a calendar or
Outlook you will see that what it SHOULD display would
be "1" as this is the first day in the first week of 2004.

If you replace the date above with "2003-12-28" you will
get "52" as result exactly like it should be. If you try
with "2003-12-30" you will get "1" as it also should be.

It isn't any problem at all to create a function by my
own for getting the weeknumber out of a date but wouldn't
you want the "standard" functions to work in the first
place?

Do you Microsoft-gurus have any answer to this or is this
a known bug that just hasn't been solved yet?

/Johan ?hrn
B.Sc.C.E.

If you're doing this in Excel, I'v a worksheet function for you:
=INT((a1-(DATE(YEAR(a1+(MOD(8-WEEKDAY(a1),7)-3)),1,1))-3+MOD(WEEKDAY(DATE(YE
AR(a1+(MOD(8-WEEKDAY(a1),7)-3)),1,1))+1,7))/7)+1
Where a1 is the cell containing the data you want the week for -
it should be fairly easy to conver this to a general VBA function - in fact
I'll give you one straight off :-)

Public Function WeekNo(D As Date) As Long
Application.Volatile False
   D = Int(D)
   WeekNo = DateSerial(Year(D + (8 - WeekDay(D)) Mod 7 - 3), 1, 1)
   WeekNo = ((D - WeekNo - 3 + (WeekDay(WeekNo) + 1) Mod 7)) \ 7 + 1
End Function

/Lars Hammarberg
www.camako.se

Hello.

I posted this yesterday in the
microsoft.public.excel.programming newsgroup but didn't
get any response so I'll try this one instead since it
might be more suitable! :)

Also, I've searched through the bug center with no luck
and also tried to find some place where you can submit
new bugs but without finding anything. To the problem:

I recently discovered something that must be a bug when
using the DatePart-function in VBA and VBScript (probably
also in VB). This also occurs when doing a similar thing
with the Format-function in VBA.

I want to get the weeknumber of a certain date. I live in
Sweden and our week starts with monday and the first week
of the year is the one with first four days of January.
This means I should use vbMonday and vbFirstFourDays. We
also write dates like this: 2002-11-26.

So to an easy example of this (and it doesn't matter if
the date has been sent in string or date string format):

Sub DateTest()
    MsgBox DatePart("ww", "2003-12-29", vbMonday,
vbFirstFourDays)
End Sub

If you try this out you will get the messagebox to
display "53". When looking this up in a calendar or
Outlook you will see that what it SHOULD display would
be "1" as this is the first day in the first week of 2004.

If you replace the date above with "2003-12-28" you will
get "52" as result exactly like it should be. If you try
with "2003-12-30" you will get "1" as it also should be.

It isn't any problem at all to create a function by my
own for getting the weeknumber out of a date but wouldn't
you want the "standard" functions to work in the first
place?

Do you Microsoft-gurus have any answer to this or is this
a known bug that just hasn't been solved yet?

/Johan ?hrn
B.Sc.C.E.