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

J.E. McGimpsey wrote:
 > In article <uN5Tm3uvDHA.2076 RemoveThis @TK2MSFTNGP09.phx.gbl>,
 > Per Bergland <per.berglandNO RemoveThis @SPAMcarmenta.se> wrote:
 >
 >
  >>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?
 >
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.

<a style='text-decoration: underline;' href="http://www.cpearson.com/excel/DateTimeVBA.htm" target="_blank">www.cpearson.com/excel/DateTimeVBA.htm</a>

but many people will be unhappy until MS decides to ship a bugfix for this.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Major bug in week numbering for 2004 (Format(..,"ww",..)) 

Hi,

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

The Mac folks are easy to contact via this URL:
<a style='text-decoration: underline;' href="http://www.microsoft.com/mac/feedback/suggestion.asp" target="_blank">http://www.microsoft.com/mac/feedback/suggestion.asp</a>

The Windows team is reachable here:
<a style='text-decoration: underline;' href="http://register.microsoft.com/mswish/suggestion.asp" target="_blank">http://register.microsoft.com/mswish/suggestion.asp</a>

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:
<a style='text-decoration: underline;' href="http://www.mvps.org/" target="_blank">http://www.mvps.org/</a>

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

----------
In article <u#6y46szDHA.832@TK2MSFTNGP09.phx.gbl>, Per Bergland
<per.berglandNO.DeleteThis@SPAMcarmenta.se> wrote:


 >
 > J.E. McGimpsey wrote:
  >> In article <uN5Tm3uvDHA.2076.DeleteThis@TK2MSFTNGP09.phx.gbl>,
  >> Per Bergland <per.berglandNO.DeleteThis@SPAMcarmenta.se> wrote:
  >>
  >>
   >>>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?
  >>
 > 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.
 >
<font color=purple> > <a style='text-decoration: underline;' href="http://www.cpearson.com/excel/DateTimeVBA.htm</font" target="_blank">www.cpearson.com/excel/DateTimeVBA.htm</font</a>>
 >
 > but many people will be unhappy until MS decides to ship a bugfix for this.<!-- ~MESSAGE_AFTER~ -->
 >> Stay informed about: Major bug in week numbering for 2004 (Format(..,"ww",..))