Re: [office-formula] WEEKNUM reference

["David A. Wheeler" <dwheeler@dwheeler.com>]

David A. Wheeler:
> > I propose that we call this the "traditional week number". It simply declares that January 1 is the first day of the first week, and increments on the next beginning of the week (controlled by the second parameter).
> 
> Something like that.
> 
> > Indeed, the Excel formula only allows Sunday and Monday, but it appears that in many Islamic countries the day begins on Saturday.  I believe we should extend that parameter to include ANY day, particularly Saturday, to handle everyone.  That would really improve internationalization.
> 
> Actually that's not enough. To determine the correct week number you
> need the first day of the week, and the minimal amount of days of a week
> there must be from Jan-1 on to number the week #1. Hence for ISO 8601
> first day of week Monday and at least 4 days of the week from Jan-1 on,
> resulting in rules like "first Thursday in year is week #1".
> Theoretically 49 combinations, practically used only a few.

Hmm, another function with possibly messy semantics.  Here are my thoughts, I'd love to hear back from others.  This turned out to be long.

====================

I think the Wikipedia article (as I currently see it) has several errors. In any case, I ALWAYS  try to look at multiple sources. I did a general search for week number info. Some useful info is here:
http://www.cpearson.com/excel/weeknum.htm
http://www.rondebruin.nl/weeknumber.htm
http://www.thescripts.com/forum/thread203224.html
http://www.onlineconversion.com/day_week_number.htm
http://www.proesite.com/timex/wkcalc.htm
http://konsulent.sandelien.no/VB_help/Week/
http://www.merlyn.demon.co.uk/weekcalc.htm

I find three other areas: (1) Simple/absolute weeks, (2) min-number-of-days weeks, and (3) years not beginning Jan. 1.  Let's look at each in turn.

1. Simple/absolute weeks

A number of these sources describe another system for week numbering that we don't handle at all: an "absolute" or "simple" WEEKNUM.  In this system, January 1 is always the start of week 1 (as with WEEKNUM), January 8 is always the start of week 2, and so on.  IE, the "beginning" of the week is whatever day was January 1.  This means that all weeks are 7 days except the last week of the year, and its appeal is simplicity.  Its repeated mentions suggest that we should handle this case.

We can cover the "absolute/simple" week number system with a trivial modification to WEEKNUM.  Here's a proposal: if WEEKNUM's second parameter is "0", then the simple week number is returned.  That would mean that WEEKNUM(d;0) == WEEKNUM(x; WEEKDAY(DATE(1;1;YEAR(x))).  I suppose you could use "-1" as the signal instead of 0, but using 0 as the signal would leave negative numbers available for future (ab)use.  That, plus allowing 1..7 for the day of the week starting, and a separate ISOWEEK, seems to handle many cases.

2. Minimum number of days/week

The "minimum number of days" criteria seems to be pretty rare except for ISO (and that's already handled in ISOWEEKNUM).  It DOES exist, e.g.:
http://icu.sourceforge.net/apiref/icu4c/ucal_8h.html
http://www.robvanderwoude.com/index.html

I have concerns if we support that too. If you TRULY wanted to handle all of the "min number of days in a week" cases, you could add a third parameter: "minimum number of days for a week".  But if the first days of a year don't have that number, what do you do?  Is it in the previous year?  Does it merge into the next week? ISO has an answer (previous year), but is that answer enough? I hate the idea of adding ANOTHER parameter to say "what do you do if there aren't enough days".

If we add a "minimum number of days for a week" argument, it should be a third parameter with the meaning "considered part of previous week", as per ISO.  I _think_ that would mean that WEEKNUM(d; 2; 4) would be identical to the ISOWEEKNUM, though I haven't investigated that in depth.

3. Years not starting Jan. 1.

Oh, and it gets worse.. some organizations start their years on days other than Jan. 1, and have week numbers that correspond.  To handle THEM you'd need to also supply a "what's the first day" argument too :-( :-(.

I hate that idea.


IN CONCLUSION:

I like the idea of allowing 1..7 in the second parameter, so we can handle Islamic practices (and because it's an obvious generalization that makes the function more regular anyway).  Then:

1. I also like the idea of allowing "0" as a second parameter, to permit "Simple" week numbers. That's easy to do, simple week numbers ARE used (I find many references to them), and it's unlikely that someone will add "0" with a different meaning. I can easily see others disagreeing with me on adding the "0" stuff for simple weeks :-).

2. I'm less excited about the "min number of days in a week", but we could add that as a 3rd argument.  I propose that we say that years beginning without enough days follow ISO's lead (part of previous year).

3. I think trying to handle non-Jan-1 years is too much.

> Apart from that only the first day of week wouldn't be enough,
> I hesitate to make such changes in a function that is provided to be
> Excel compatible.

This isn't just an "Excel compatible" function; lots of people NEED a WEEKNUM function.  I don't know of ANY company in the U.S. that uses ISO week numbers, for example; they all expect the WEEKNUM semantics as provided by Excel. Excel doesn't even IMPLEMENT the ISO week numbering ability as a function; it only implements the old WEEKNUM semantics.  Yes, this is another example of Excel's failure to implement standards, but this "traditional" semantic IS widespread.

We can do much better, of course; we can implement a good WEEKNUM (with at least 0..7) and an ISOWEEKNUM so that the users can get whatever they need to get.

--- David A. Wheeler