2

According to the ECMA-376, Second Edition, Part 1 - Fundamentals And Markup Language Reference SpreadsheetML supports date serial values with three possible compatibility settings and various ranges.

http://www.documentinteropinitiative.com/implnotes/ISO-IEC29500-2008/001.018.017.004.001.000.000.aspx

  • In the 1900 date base system, the lower limit is January 1, -9999, which has serial value -4346018. The base date for this date base system is December 30, 1899, which has a serial value of 0.
  • In the 1900 backward compatibility date-base system, the lower limit is January 1, 1900, which has serial value 1. The base date for this date base system is December 31, 1899, which has a serial value of 0.
  • In the 1904 backward compatibility date-base system, the lower limit is January 1, 1904, which has serial value 0. The base date for this date base system is January 1, 1904, which has a serial value of 0.

However, when I use Microsoft Office Excel 2010 to generate a *.xlsx file with a date serial of 1, the *.xlsx file does not have any dateCompatibility attribute set (indicating "1900 date base system") but the date associated with date serial 1 is January 1, 1900 (indicating "1900 backward compatibility").

The workbookPr element is this, unedited, no dateCompatibility attribute.

<x:workbookPr defaultThemeVersion="124226" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main" />

And here is a row of data that shows a date serial of 1 and the text result of formatting it as a date:

   <x:row r="3" spans="1:2">
     <x:c r="A3">
       <x:v>1</x:v>
     </x:c>
     <x:c r="B3" t="str">
       <x:f t="shared" si="0" />
       <x:v>1900-01-01 00:00:00.0</x:v>
     </x:c>
   </x:row>

the formula is just date formatting, referenced earlier in the worksheet:

    <x:f t="shared" ref="B1:B42" si="0">TEXT(A1, "yyyy-mm-dd HH:mm:ss.0")</x:f>

This formula gives the same result as applying a date format pattern to the date serial number, demonstrated in the below screen shot.

alt text

Also formatting negative dates results in an error, another indicator that Excel is actually using backwards-compatible date system despite the absence of the dateCompatibility attribute on workbookPr.

The options for the workbook only show an unchecked checkbox for 1904 date system. I don't see any option for 1900 date based vs. 1900 backwards-compatibility.

Am I reading the spec wrong? Or the SpreadsheetML? Or something else?

Samuel Neff
  • 73,278
  • 17
  • 138
  • 182

1 Answers1

2

The default implementation in Excel is dateCompatibility="1" (i.e. 1900 backward compatibility), so if it is absent, it is still dateCompatibility="1". Note that Excel doesn't fully implement SpreadsheetML (as Word and PowerPoint also do not fully implement their respective MLs and with 2010 client versions, extend the ML in ways not supported by the standard(s)). 1 and 3 don't appear to be implemented (very well) in conversion of serial values to date values.

There's been some push-back to ECMA on this and the use of date1904, see: http://xmlguru.cz/2008/01/ecma-response-to-czech-ooxml-comments and search on dateCompatibility

Todd Main
  • 28,951
  • 11
  • 82
  • 146
  • Thanks for pointing this out. Now that you mentioned it, I found that the default value is for "backwards compatibility" mode elsewhere in the spec. I also noticed that it codifies 1900 as a leap year for "backwards compatibility". Love that little branch in my translation code. :-) `if (dateCompatibility == DateCompatibility.BackwardsCompatibleBase1900 && dateSerial >= 61.0) { dateSerial -= 1; }` – Samuel Neff Jan 23 '11 at 01:55
  • Yes, the 1900 as a leap year is actually a throwback to the early versions of Lotus 1-2-3; when Excel needed compatibility with that – Mark Baker Jan 23 '11 at 15:24