[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]

*Subject*: **Re: [office-formula] WEEKNUM reference**

*From*:**Eike Rathke <erack@sun.com>***To*: office-formula@lists.oasis-open.org*Date*: Thu, 22 Feb 2007 01:31:04 +0100

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.

**Follow-Ups**:**Re: [office-formula] WEEKNUM reference***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**Re: [office-formula] WEEKNUM reference***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**References**:**WEEKNUM reference***From:*Eike Rathke <erack@sun.com>

**Re: [office-formula] WEEKNUM reference***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

**Re: [office-formula] WEEKNUM reference***From:*Eike Rathke <erack@sun.com>

**Re: [office-formula] WEEKNUM reference***From:*"David A. Wheeler" <dwheeler@dwheeler.com>

[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]