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:
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/com...dg=microsoft.public.excel.worksheet.functions
Isn't Jan 3rd week 53 of 2009?"Paul Janssen" wrote:
Isn't Jan 3rd week 53 of 2009?
:
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.
:
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/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,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
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/tutorial...6-54f31bdede5d/eggheadcafe-chat-chaos-in.aspx