Weeknum in 2010 incorrect
in [Functions]
|
Prev: Countif problems
Next: Sum Workdays Solutions
From: Paul Janssen Paul on 25 Jan 2010 07:30 Hello, we encountered a faulty weeknumber generated by MX Excel for all dates in 2010 as of Jan 3d, 2010. This should be weeknumber 1 until January 9th, 2010 but MX Excel uses weeknumber 2. As a result all consecutive weeks in 2010 are numbered 1 week too high. Would be nice if MS could issue a repair patch! regards, Paul Janssen ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=c75660aa-7129-4fba-a6fd-4aa0ffc18c11&dg=microsoft.public.excel.worksheet.functions
From: trip_to_tokyo on 25 Jan 2010 07:46 Isn't Jan 3rd week 53 of 2009? "Paul Janssen" wrote: > Hello, we encountered a faulty weeknumber generated by MX Excel for all dates > in 2010 as of Jan 3d, 2010. This should be weeknumber 1 until January 9th, > 2010 but MX Excel uses weeknumber 2. As a result all consecutive weeks in > 2010 are numbered 1 week too high. > > Would be nice if MS could issue a repair patch! > > regards, Paul Janssen > > ---------------- > This post is a suggestion for Microsoft, and Microsoft responds to the > suggestions with the most votes. To vote for this suggestion, click the "I > Agree" button in the message pane. If you do not see the button, follow this > link to open the suggestion in the Microsoft Web-based Newsreader and then > click "I Agree" in the message pane. > > http://www.microsoft.com/office/community/en-us/default.mspx?mid=c75660aa-7129-4fba-a6fd-4aa0ffc18c11&dg=microsoft.public.excel.worksheet.functions
From: Mike H on 25 Jan 2010 07:56 From E2007 help Important The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Paul Janssen" wrote: > Hello, we encountered a faulty weeknumber generated by MX Excel for all dates > in 2010 as of Jan 3d, 2010. This should be weeknumber 1 until January 9th, > 2010 but MX Excel uses weeknumber 2. As a result all consecutive weeks in > 2010 are numbered 1 week too high. > > Would be nice if MS could issue a repair patch! > > regards, Paul Janssen > > ---------------- > This post is a suggestion for Microsoft, and Microsoft responds to the > suggestions with the most votes. To vote for this suggestion, click the "I > Agree" button in the message pane. If you do not see the button, follow this > link to open the suggestion in the Microsoft Web-based Newsreader and then > click "I Agree" in the message pane. > > http://www.microsoft.com/office/community/en-us/default.mspx?mid=c75660aa-7129-4fba-a6fd-4aa0ffc18c11&dg=microsoft.public.excel.worksheet.functions
From: "David Biddulph" groups [at] on 25 Jan 2010 07:56 It is not correct to describe Excel's output as "faulty". It follows the not-unreasonable convention that puts January 1st in week 1. The algorithm is shown in http://office.microsoft.com/en-gb/excel/HP052093371033.aspx, and there is a clear warning that other people may use different conventions. You will find some of those alternative conventions outlined at http://en.wikipedia.org/wiki/Seven-day_week#Week_numbering. There is plenty of published information on alternative strategies for week numbers in Excel, including at http://www.rondebruin.nl/weeknumber.htm and at http://www.cpearson.com/Excel/WeekNumbers.aspx -- David Biddulph "Paul Janssen" <Paul Janssen(a)discussions.microsoft.com> wrote in message news:C75660AA-7129-4FBA-A6FD-4AA0FFC18C11(a)microsoft.com... > Hello, we encountered a faulty weeknumber generated by MX Excel for all > dates > in 2010 as of Jan 3d, 2010. This should be weeknumber 1 until January 9th, > 2010 but MX Excel uses weeknumber 2. As a result all consecutive weeks in > 2010 are numbered 1 week too high. > > Would be nice if MS could issue a repair patch! > > regards, Paul Janssen > > ---------------- > This post is a suggestion for Microsoft, and Microsoft responds to the > suggestions with the most votes. To vote for this suggestion, click the "I > Agree" button in the message pane. If you do not see the button, follow > this > link to open the suggestion in the Microsoft Web-based Newsreader and then > click "I Agree" in the message pane. > > http://www.microsoft.com/office/community/en-us/default.mspx?mid=c75660aa-7129-4fba-a6fd-4aa0ffc18c11&dg=microsoft.public.excel.worksheet.functions
From: Ron Rosenfeld on 25 Jan 2010 08:54
On Mon, 25 Jan 2010 04:30:01 -0800, Paul Janssen <Paul Janssen(a)discussions.microsoft.com> wrote: >Hello, we encountered a faulty weeknumber generated by MX Excel for all dates >in 2010 as of Jan 3d, 2010. This should be weeknumber 1 until January 9th, >2010 but MX Excel uses weeknumber 2. As a result all consecutive weeks in >2010 are numbered 1 week too high. > >Would be nice if MS could issue a repair patch! > >regards, Paul Janssen > Paul, It really depends on how you define weeknumber. The Excel WEEKNUM function behaves exactly as documented in HELP. "The WEEKNUM function considers the week containing January 1 to be the first week of the year." And you can choose whether your week should start on Sunday or Monday. If you want your weeks to start on Monday, then enter the appropriate return argument into the function and Jan 3, 2010 will fall into Week 1, giving you the results you apparently desire. I will also point out that there is an ISO standard for weeknumbers, which I'm told is used extensively in Europe. However, that standard would not return week 1 for 2010-Jan-3 through 2010-Jan-9 as that standard calls for weeks to start on Monday; and 2010-Jan-3 is a Sunday (and so would be week 53 under that standard). --ron |