Bug in Format function for dates
Tom van Stiphout
6.0.8714
In our company the business rule is that a week starts on a Monday, and the
first week of the year is the week with at least 4 days in that year. Weekly
reports are marked using the ww/yyyy (weeknumber/year) format.
Run this in your immediate window:
?Format$(#12/24/2001#, "ww/yyyy", vbMonday, vbFirstFourDays)
-> 52/2001 (correct)
?Format$(#12/31/2001#, "ww/yyyy", vbMonday, vbFirstFourDays)
-> 1/2001 (INCORRECT)
?Format$(#1/1/2002#, "ww/yyyy", vbMonday, vbFirstFourDays)
-> 1/2002 (correct)
?Format$(#1/7/2002#, "ww/yyyy", vbMonday, vbFirstFourDays)
-> 2/2002 (correct)
Clearly the one for 12/31/2001 is incorrect. Sounds like a legitimate bug to
me. Access 97 has the same problem. I don't have an Office XP machine
here; can anyone verify this on that
platform?
Regards,
-Tom.
Clive Bolton
bug prior to A97 also. I have not yet needed to check on XP.
My current A97/A2K routines for meeting European importers' requirements
are:
^^^^^^^^
Public Function getISODate(mydate As Variant) As String
Dim yrint As Integer
Dim wkint As Integer
Dim dyint As Integer
Dim doyint As Integer
Dim rslt As String
On Err GoTo procerr
rslt = "0000.00.0"
If IsDate(mydate) Then
yrint = DatePart("yyyy", mydate)
wkint = DatePart("ww", mydate, vbMonday, vbFirstFourDays)
dyint = DatePart("w", mydate, vbMonday, vbFirstFourDays)
doyint = DatePart("y", mydate)
Select Case doyint
Case Is < 4
If dyint > 4 Then yrint = yrint - 1
Case Is > 362
If dyint < 4 Then yrint = yrint + 1
End Select
rslt = Str$(yrint) & "." & Right$("0" & wkint, 2) & "." &
Right$(Str$(dyint), 1)
End If
procexit:
On Error Resume Next
getISODate = rslt
Exit Function
procerr:
MsgBox Err.Description
Resume procexit
End Function
^^^^^^^^
Public Function getISOWk(mydate As Variant) As String
getISOWk = Mid(getISODate(mydate), 2, 7)
End Function
^^^^^^^^
Clive
"Tom van Stiphout" <tvanst...@kinetik-it.com> wrote in message
news:u3rt1oc...@corp.supernews.com...
MShe...@compuserve.com
<tvanst...@kinetik-it.com> wrote:
[snip]
>In our company the business rule is that a week starts on a Monday, and the
>first week of the year is the week with at least 4 days in that year. Weekly
>reports are marked using the ww/yyyy (weeknumber/year) format.
[snip]
>?Format$(#12/31/2001#, "ww/yyyy", vbMonday, vbFirstFourDays)
>-> 1/2001 (INCORRECT)
[snip]
>Clearly the one for 12/31/2001 is incorrect.
Incorrect? Doesn't the first week of 2002 begin on Monday,
31-Dec-2001, and end on Sunday, 06-Jan-2002?
Nevertheless, there's still a bug in oleaut32.dll to deal with. See
KB article Q200299, "Format or DatePart Functions Can Return Wrong
Week Number". Without implementing the workaround, you're good until
29-Dec-2003.
--
Mike Sherrill
Information Management Systems
Dimitri Furman
<tvanst...@kinetik-it.com> wrote in
news:u3rt1oc...@corp.supernews.com:
> ?Format$(#12/31/2001#, "ww/yyyy", vbMonday, vbFirstFourDays)
> -> 1/2001 (INCORRECT)
>
> Clearly the one for 12/31/2001 is incorrect. Sounds like a legitimate
> bug to me. Access 97 has the same problem. I don't have an Office XP
> machine here; can anyone verify this on that
> platform?
>
Same result in Access 2002.
--
(remove a 9 to reply by email)
Joe "Nuke Me Xemu" Foster
This issue has popped up in the VB groups too. At first, I figured all
that had to be done was to find the first day of the week and pass that
to Format$ instead. Hijinks ensued...
--
Joe Foster <mailto:jlfoster%40znet.com> On the cans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Tom van Stiphout
Thanks, I had not seen this article before. Technically it discusses a
slightly different behavior, but I'm sure it's related.
Please look carefully: the function returns 1/2001, not 1/2002 as you
would expect.
Isn't is disgusting MSFT has known about this bug for 2 years or more,
and has not deemed it important enough to bother fixing it? We almost
paid our salesreps twice the commission they were owed, thanks to
this.
-Tom.
MShe...@compuserve.com
<no.spam...@home.com> wrote:
>On Fri, 11 Jan 2002 02:07:17 GMT, MShe...@compuserve.com wrote:
>
>Thanks, I had not seen this article before. Technically it discusses a
>slightly different behavior, but I'm sure it's related.
The week number issue is the same issue--a bug in oleaut32.dll
>Please look carefully: the function returns 1/2001, not 1/2002 as you
>would expect.
That's a different problem. The arguments to the Format() function
all "operate" on the date you pass as its first argument. The year
associated with 31-Dec-2001 is 2001. You'll need to build your own
function to work around that one. Air code:
Public Function WeekAndYear(ByVal Any_Date As Date) As String
Dim The_Week As Integer, The_Year As Integer
The_Week = CInt(Format(Any_Date, "ww", vbMonday, vbFirstFourDays))
The_Year = Year(Any_Date)
If (The_Week = 1) And (Month(Any_Date) = 12) Then
The_Year = The_Year + 1
End If
WeekAndYear = The_Week & "/" & The_Year
End Function
Note that this doesn't implement the logic you need to deal with the
oleaut32.dll bug, only the logic you need to get the right year.
>Isn't is disgusting MSFT has known about this bug for 2 years or more,
>and has not deemed it important enough to bother fixing it?
Get used to it. (Although I don't believe they allocate resources the
way you'd think.)