Groups | Search | Server Info | Login | Register

Groups > comp.apps.spreadsheets > #74

Re: Long spreadsheets

Show key headers only | View raw

On Saturday, 10 November 2018 19:09:43 UTC, Eike Rathke  wrote:
> * dr.s.lartius@gmail.com, 2018-11-06 23:29 UTC:

> > ASIDE - general spreadsheets - it should be possible to have a numeric column formatted as Date, with the dates being in any of the three main ISO8601 forms - yyyy-mm-dd, yyyy-Www-d, & yyyyddd.  Please.  Don't trust US coders (NIST apart) to calculate yyyy-Www-d correctly for all dates.
> 
> I don't quite understand what you mean with Www, if it's abbreviated
> month name then it is MMM, if it is the week number then it would be WW
> but then yyyy-Www-d wouldn't make sense. Anyhow, yyyy-mm-dd is
> recognized as date as is yyyy-mmm-dd but of course the actual month name
> abbreviation recognized depends on the current locale. A numeric yyyyddd
> input can't be a date because it is a number.


You should read ISO 8601, or at least https://en.wikipedia.org/wiki/ISO_8601.

I should have, for consistency, put yyyy-ddd ; but the condensed forms without the '-' characters are also standard and are unambiguous for a string which is known to be an ISO 8601 date.

The 'W' indicates that a Week number (range 01 to 53) follows, and a day-of-week (from Mon=1) follows.  All Weeks have seven days.  Week 01 contains the first Thursday of the Gregorian year.  The ISO Week Number appears (without explanation) in the bar-code box of my daily newspaper.

A seven-digit string without 'W' must be yyyyddd meaning yyyy-ddd, where ddd is the ordinal date in the year, from 000 to 366.

Normally, 4 digits - never fewer (unless none) are used for the Year; but the Standard does say what to do for years which may be above 9999.

You will notice that each of those forms, but not a mixture, can be sorted by a simple string sort; and if the non-digits are removed, by a numeric sort.

I believe that spreadsheet date/times are generally stored as IEEE Doubles from an Epoch which was intended to be (I think) 1899-12-31 = 0; but the inventors of that failed to recall that 1900 was not a Leap Year; so yyyyddd could be a spreadsheet date.

-- 
  (c) Dr. S. Lartius, UK.  Gmail: dr.s.lartius@

Back to comp.apps.spreadsheets | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Long spreadsheets dr.s.lartius@gmail.com - 2018-11-06 15:29 -0800
  Re: Long spreadsheets Luuk <luuk@invalid.lan> - 2018-11-07 14:55 +0100
    Re: Long spreadsheets Eike Rathke <erack-nutznetz.n@posteo.de> - 2018-11-10 19:09 +0000
  Re: Long spreadsheets Eike Rathke <erack-nutznetz.n@posteo.de> - 2018-11-10 19:09 +0000
    Re: Long spreadsheets dr.s.lartius@gmail.com - 2018-11-10 14:56 -0800
      Re: Long spreadsheets Eike Rathke <erack-nutznetz.n@posteo.de> - 2018-11-11 11:04 +0000
        Re: Long spreadsheets dr.s.lartius@gmail.com - 2018-11-11 04:04 -0800
          Re: Long spreadsheets Eike Rathke <erack-nutznetz.n@posteo.de> - 2018-11-11 23:08 +0000
            Re: Long spreadsheets Luuk <luuk@invalid.lan> - 2018-11-17 14:00 +0100
              Re: Long spreadsheets Eike Rathke <erack-nutznetz.n@posteo.de> - 2018-11-18 01:17 +0000

csiph-web