OASIS Mailing List ArchivesView the OASIS mailing list archive below
or browse/search using MarkMail.


Help: OASIS Mailing Lists Help | MarkMail Help

office message

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

Subject: Re: [office] Date and Timezone: draft text

For reference, here is how OOXML defines it:


18.17.4 Dates and Times

Each unique instant in SpreadsheetML time is stored as an ISO 8601-formatted string, which is made up of a date component, a time component, and a timezone component. Numerous functions take dates and/or times as arguments. Functions that care only about the date shall ignore any time information that is provided. Functions that care only about the time shall ignore any date information
that is provided.

[Example: The date 22 November 1976 at exactly 08:30 Pacific Standard Time (+08:00 UTC) could be represented in the following (non-exhaustive list of) ways within SpreadsheetML:


end example]

For compatibility with existing spreadsheet applications, a consuming application should allow certain numeric serial values to be interpreted as dates and times for display or for use in calculations. These values should behave as defined in,, and


However, it then says in


"A serial value represents a UTC date and time, and, as such, has no timezone information."


No Excel versions (that I'm aware of) support ISO 8601 dates as described in 18.17.4.  They all use the date serial numbers and treat these serial numbers as described in, where date serial numbers represent a UTC data/time with no timezone info.

Consider what would happen if dates were treated as having serial numbers.  I enter annual, sales forcastes in a spreadsheet, 1/1/2000, 1/1/2001, 1/1/2002, etc.  I format them to show only the date, not the  time.  But internally they are stored as 2000-01-01T00:00:00+5:00.  I send the spreadsheet to someone in Chicago, one time zone to the West from me.  If displayed in local time, the dates will all display a year earlier.  And all calculations looking at just the year value would be wrong. Why? Because midnight January 1st corresponds to 11pm December 31st the prior year.

This can be fixed.   It would require that we also add a setting for dates, orthogonal to the display format, that tells what timezone to show the date value in.  Local time zone?  A specific time zone?  Original time zone?

However, I do not recommend doing this for ODF 1.2.  First, no existing spreadsheet application does timezone calculations with datetime values.  Second, our goal for the first release of OpenFormula was to precisely and accurately describe the way spreadsheet formulas currently work.  

David, I wonder if we want to explicitly state that the OpenFormula dates are explictly in UTC instead of "unspecified time zone"?  This would give us something definite to work with if we decided to add timezone sensitivity in a future version.


"David A. Wheeler" <dwheeler@dwheeler.com> wrote on 07/05/2008 01:39:48 PM:

> Here's draft text to clarify that timezone is NOT recorded
> in the formula work; comments?  I intend to put this in the section
> on Date and Datetime subtypes.  This will (hopefully) resolve the issue,
> as discussed in the last TC teleconference.
> =====
> The Date and Datetime subtypes (number of days since the
> beginning of the epoch) do not include a mechanism for recording the
> time zone.  Instead, these subtypes only presume that all
> date or datetime values use the same unspecified time zone.
> Document creators CAN use formulas to do time zone
> translations using formulas, and CAN decide on a particular single time zone
> such as UTC (Coordinated Universal Time) when developing a
> particular document.
> Similarly, the Date and Datetime subtypes do not include a mechanism
> for distinguishing the value of a leap second and the following second,
> and presume that every day is exactly 24 hours long.
> --- David A. Wheeler
> ---------------------------------------------------------------------
> To unsubscribe from this mail list, you must leave the OASIS TC that
> generates this mail.  You may a link to this group and all your TCs in OASIS
> at:

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