|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 25th, 2010, 12:30 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weeknum in 2010 incorrect
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/comm...et.f unctions 
|
|
#2
January 25th, 2010, 12:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weeknum in 2010 incorrect
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/comm...et.f unctions
|
|
#3
January 25th, 2010, 12:56 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weeknum in 2010 incorrect
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/comm...et.f unctions
|
|
#5
January 25th, 2010, 01:54 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weeknum in 2010 incorrect
|
|
#6
January 27th, 2010, 01:53 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Weeknum in 2010 incorrect
On Mon, 25 Jan 2010 08:54:43 -0500, Ron Rosenfeld
wrote: On Mon, 25 Jan 2010 04:30:01 -0800, Paul Janssen Paul 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 I find it annoying that in a year that has always been only 52 weeks long, that a standards organization would adopt a "standard" where a "week 53" gets utilized. It just caused an issue on our production floor two weeks ago with respect to date coding for product serialization purposes, which is where the entire system was derived from to begin with. So what did those *they* people arrive at 5 decades ago? I am quite sure there will be a mil standard somewhere. Regardless of what day of the week it is or how it makes one's calendar appear to the eye, the week that contains 1 January should be called "week 1". The week that contains the last 5 days of December should be called "week 52". It does not get much more simple than that. Worrying about the fact that we print a calendar with the same day at the left edge of the grid has only muddied the fact. Numbered weeks in a year do not have a set "first day of the week" Week numbering is a function of the number 7 and the number 365. That is the US std (or was). I too remember reading about the variances and alternate "conventions" being utilized, however. The dude could make a custom "WEEKNUM_EU()" function. :-)
|
|
#7
February 9th, 2010, 08:21 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
2010 Week no. incorrect
Easy solution I have found is to add -1 after the function
ie. =WEEKNUM(cell)-1 Hope this helps CellShocked wrote: I find it annoying that in a year that has always been only 52 weekslong, that 26-Jan-10 I find it annoying that in a year that has always been only 52 weeks long, that a standards organization would adopt a "standard" where a "week 53" gets utilized. It just caused an issue on our production floor two weeks ago with respect to date coding for product serialization purposes, which is where the entire system was derived from to begin with. So what did those *they* people arrive at 5 decades ago? I am quite sure there will be a mil standard somewhere. Regardless of what day of the week it is or how it makes one's calendar appear to the eye, the week that contains 1 January should be called "week 1". The week that contains the last 5 days of December should be called "week 52". It does not get much more simple than that. Worrying about the fact that we print a calendar with the same day at the left edge of the grid has only muddied the fact. Numbered weeks in a year do not have a set "first day of the week" Week numbering is a function of the number 7 and the number 365. That is the US std (or was). I too remember reading about the variances and alternate "conventions" being utilized, however. The dude could make a custom "WEEKNUM_EU()" function. :-) Previous Posts In This Thread: On Monday, January 25, 2010 7:30 AM Paul Janssen wrote: Weeknum in 2010 incorrect 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/comm...et.f unctions On Monday, January 25, 2010 7:46 AM trip_to_tokyo wrote: Isn't Jan 3rd week 53 of 2009?"Paul Janssen" wrote: Isn't Jan 3rd week 53 of 2009? "Paul Janssen" wrote: On Monday, January 25, 2010 7:56 AM Mike H wrote: From E2007 helpImportant The WEEKNUM function considers the week containing 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: On Monday, January 25, 2010 7:56 AM David Biddulph wrote: It is not correct to describe Excel's output as "faulty". 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/ex...093371033.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-d...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 On Monday, January 25, 2010 8:54 AM Ron Rosenfeld wrote: Paul,It really depends on how you define weeknumber. 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 am 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 On Tuesday, January 26, 2010 8:53 PM CellShocked wrote: I find it annoying that in a year that has always been only 52 weekslong, that I find it annoying that in a year that has always been only 52 weeks long, that a standards organization would adopt a "standard" where a "week 53" gets utilized. It just caused an issue on our production floor two weeks ago with respect to date coding for product serialization purposes, which is where the entire system was derived from to begin with. So what did those *they* people arrive at 5 decades ago? I am quite sure there will be a mil standard somewhere. Regardless of what day of the week it is or how it makes one's calendar appear to the eye, the week that contains 1 January should be called "week 1". The week that contains the last 5 days of December should be called "week 52". It does not get much more simple than that. Worrying about the fact that we print a calendar with the same day at the left edge of the grid has only muddied the fact. Numbered weeks in a year do not have a set "first day of the week" Week numbering is a function of the number 7 and the number 365. That is the US std (or was). I too remember reading about the variances and alternate "conventions" being utilized, however. The dude could make a custom "WEEKNUM_EU()" function. :-) Submitted via EggHeadCafe - Software Developer Portal of Choice EggHeadCafe Chat Chaos in Silverlight Released Today http://www.eggheadcafe.com/tutorials...-chaos-in.aspx
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
