Major bug in week numbering for 2004 (Format(..,"ww",..))

[Per Bergland]

I just discovered that week numbering, Nordic style, for 2004 doesn't
work in my Excel X 10.1.4 (on Panther).

Try this in your VBA code:

x = Format(#1/1/2004#, "ww", vbMonday, vbFirstFourDays)

and notice that in Mac Office X it returns "53" instead of the correct
answer "1" (the week containing jan 1, 2004 is the first week in 2004
that contains 4 days when the week starts on a monday - the function
probably assumes that the week starts on a Sunday).

Can anyone shed some light on this or come up with an alternate way of
getting the week number from a date without installing "analysis
toolpak" (I wonder if that addin works...)?

 

[J.E. McGimpsey]

I just discovered that week numbering, Nordic style, for 2004 doesn't
work in my Excel X 10.1.4 (on Panther).

Try this in your VBA code:

x = Format(#1/1/2004#, "ww", vbMonday, vbFirstFourDays)

and notice that in Mac Office X it returns "53" instead of the correct
answer "1" (the week containing jan 1, 2004 is the first week in 2004
that contains 4 days when the week starts on a monday - the function
probably assumes that the week starts on a Sunday).

Can anyone shed some light on this or come up with an alternate way of
getting the week number from a date without installing "analysis
toolpak" (I wonder if that addin works...)?

When I run it in the immediate pane of the VBE (XL 10.1.5, build
030814, US English), I get

x = Format(#1/1/2004#, "ww", vbMonday, vbFirstFourDays)
?x
1

What language version of Office are you running?

I tried changing my system date settings with no effect on the above.

 

[Per Bergland]

When I run it in the immediate pane of the VBE (XL 10.1.5, build
030814, US English), I get

x = Format(#1/1/2004#, "ww", vbMonday, vbFirstFourDays)
?x
1

What language version of Office are you running?

Swedish Office, Swedish regional settings.
Btw, I tried again on Windows (Excel 2002), and the bug appears there
too for the Monday in week 1, 2004

Sub xxx()
Dim s As String
Dim d As Date
d = #12/29/2003#
MsgBox Format(d, "ww", vbMonday, vbFirstFourDays) & " should be 1"
' However, Tuesday etc work fine on Windows
End Sub

This has already broken one of our internal Access apps, and is quite
likely to break thousands more in those parts of Europe that rely on
standardized ISO week numbers.

And just for the fun of it, I tried the Analysis Toolpak WEEKNUM(xxx,2)
function, and it returns 53 for all 3 days in December 2003 that fall in
the first week of 2004.

Of course there are workarounds, e.g.

www.cpearson.com/excel/DateTimeVBA.htm

but many people will be unhappy until MS decides to ship a bugfix for this.

 

[Jim Gordon MVP]

Hi,

Please be sure to take a moment to contact Microsoft with this information.

The Mac folks are easy to contact via this URL:
http://www.microsoft.com/mac/feedback/suggestion.asp

The Windows team is reachable here:
http://register.microsoft.com/mswish/suggestion.asp

Although your posting is helpful to those reading the public newsgroups,
Microsoft employees probably won't see your posting here. If you send
feedback to the URLs above you can be sure that product managers will read
and track this problem even if they don't respond to you personally. This
sounds like a problem that they would like to know about and fix.

-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Search for help with the free Google search Excel add-in:
<http://www.rondebruin.nl/Google.htm>

----------