Re: [office-formula] WEEKNUM reference

[Eike Rathke <erack@sun.com>]

Hi David,

On Friday, 2007-02-16 13:33:12 -0500, David A. Wheeler wrote:

> 1. Simple/absolute weeks
> 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))).

Apart from that it would be DATE(YEAR(x);1;1) instead, I don't see why
WEEKNUM(d;0) would be equal to WEEKNUM(x;n) with n!=0.

> 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

Actually quite a few locales not using ISO 8601 week numbering use
minimum-number-of-days 1, resulting in the first week of the year being
the week where the first start-of-week-day of that year is, which would
be covered by your 1..7 proposal above.

Some, similar to ISO 8601, use minimum-number-of-days 4, but with Sunday
as start-of-week-day. These would need extra handling. Currently the
locales are *-CA Canada and *-MT Malta, according to the CLDR
supplementalData.xml

> 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".

I never heard of "week 0", and IMHO all week numberings that do not use
minimum-number-of-days 1 assign the previous days to week 52
respectively 53. I may be wrong on this though.

> 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.

Looks like, yes.


> 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.

Me too.. these organizations probably could do some offset calculation
or use their own Add-In function if supported by the application.


> 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.

I just thought of the possibility that Excel might follow the idea of
enhancing the WEEKNUM functionality, but use a different numbering in
the parameters. Currently it generates a #NUM! error for values other
than 1 or 2 as the second argument, but in that case documents exported
to Excel would silently calculate different results. But maybe I'm too
precautious.

> 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.

With the minimum-number-of-days parameter we could also drop ISOWEEKNUM,
since it could be written as WEEKNUM(d;2;4). Do we want that?

  Eike

-- 
Automatic string conversions considered dangerous. They are the GOTO statements
of spreadsheets.  --Robert Weir on the OpenDocument formula subcommittee's list.