Warning: Google Docs & Default Times

Warning: Google Docs & Default Times

  
Tonight a co-worker emailed me an Excel spreadsheet exported from one of our Agile Platform projects. I opened it up in Google Docs, and I immediately noticed something odd: a lot of dates as "12/31/1899". Why is that odd, besides the obvious? Because Agile Platform's default value for null dates is "1/1/1900". I then downloaded the spreadsheet from Gmail and opened in Real Excel(TM). Guess what? It showed "1/1/1900" in those cells! I've attached a screenshot.

Unpacking the xlsx file into a ZIP, and opening the underlying XML reveals the issue. The date/time fields are stored as an integer (probably something like "seconds since the epoch'" with an epoch of 1/1/1900). It's a simple edge case scenario.

This means that folks need to be VERY careful when combining Excel spreadsheets for import/export and Google Docs. This can potentially cause issues. Make sure to use a BLANK field, not 12/31/1899 or 1/1/1900 for a null date if using Google Docs to work on a spreadsheet for import into Agile Platform. I have a strong suspicion that if you put 1/1/1900 in there, Agile Platform will interpret as 1/1/1900 00:00:01 and not a null date, which will cause problems if you are checking for a null date and using a default.

Of note: Excel and Agile Platform interpret the calue of "1" the same: 1/1/1900. The fault is with Google Docs, not Agile Platform.

J.Ja
What about xls file? same issue?
Don't know, didn't try it.

J.Ja
Hi,
 
This is a famous Microsoft Excel bug:
http://support.microsoft.com/kb/214058
http://www.ozgrid.com/Excel/ExcelDateandTimes.htm
http://blogs.msdn.com/brian_jones/archive/2006/10/25/spreadsheetml-dates.aspx
http://www.robweir.com/blog/2006/10/leap-back.html

Dates previous to 1900-03-01 have 1 day offset. The platform generates them so they appear correctly in the excel program. 
Guess that for google docs that doesn't work very well.