I wonder if anyone can explain or reproduce the following test case. It demonstrates that GMT-06:00 Central Time - Tegucigalpa is 668 seconds ahead of GMT-06:00 Central Time, which seems like an unreasonable result.

Document 1

Document 1 uses GMT-06:00 Central Time.
Cell A1 in document 1 contains the time value 12:00:00, numeric value 0.5.

Locale: United States
Time zone: (GMT-06:00) Central Time
Cell  Format  Value              Formula
A1    Time    12:00:00
A2    Normal  0.5                =A1

Document 2

Document 2 uses GMT-06:00 Central Time - Tegucigalpa.
Cell A1 in document 2 imports cell A1 from document 1.
The resulting time value is 12:11:08, numeric value 0.507731481480732.

Locale: United States
Time zone: (GMT-06:00) Central Time - Tegucigalpa
Cell  Format  Value              Formula
A1    Time    12:11:08           =ImportRange( "[key of Doc1]" ; "Sheet1!A1" )
A2    Normal  0.507731481480732  =A1

Old Sheets vs. New Sheets

Google introduced New Sheets after I raised this issue. Apparently New Sheets time zone conversion is incompatible with Old Sheets. Documents which depend on timezone will calculate different results depending on whether they are Old Sheets or New Sheets.

  • If Document 2 is Old Sheets, the result is 12:11:08 regardless of what Document 1 is.
  • If Document 2 is New Sheets, the result is 12:00:00 regardless of what Document 1 is.

External links

Google Sheets Anomaly: Tegucigalpa Time – a public Google Drive folder containing test cases

  • 1
    @pnuts It is still reproducible in Old Sheets. But it is not reproducible in New Sheets. I will update the question to reflect. – MetaEd Jun 25 '14 at 14:22
  • Interesting about "old" vs "new" sheets. They must have changed the import/export process. This may be a silly question, but how do you create an "old" sheet? – Matt Johnson-Pint Jun 25 '14 at 14:56
  • "Starting in March and April 2014, Google Sheets users creating new spreadsheets will be automatically directed to the new Sheets. All previously created spreadsheets will remain in the older version of Sheets for now. If you need to create a spreadsheet using the old Sheets, you can do so at g.co/oldsheets" —Check out the new Google Sheets – MetaEd Jun 25 '14 at 15:28
  • @MattJohnson Hope that the above gets you started. Now I am very curious whether Google got timezone conversion "wrong" in New Sheets. – MetaEd Jun 25 '14 at 15:46
  • 1
    @MattJohnson Yes, in fact I have "wrong" in quotes because I agree with you -- it is quite possibly an intentional design difference. But even a design can be wrong, and I think the more interesting question is how should it work, and why should it be different in New Sheets. – MetaEd Jun 25 '14 at 19:55

Time zones have gone through many changes over the years, and these changes are recorded in the IANA/Olson time zone database. Google uses this data to perform the conversions.

When values are being converted from one time zone to another, it is essential to have a date as well as a time. Since the source values you provided are time-only fields, then a date has to be assumed.

Rather than assuming the date as today, Google Spreadsheet assigns a default date of December 30th, 1899. You can see this if you add a time value to a cell, then change the cell's formatting to date and time.

So if we look at the source code repository for the time zone data, we can see the following for the two time zones in question:

# Zone  NAME                 GMTOFF    RULES  FORMAT  [UNTIL]
Zone    America/Tegucigalpa  -5:48:52  -      LMT     1921 Apr
                             -6:00     Hond   C%sT

# Zone  NAME                 GMTOFF    RULES  FORMAT  [UNTIL]
Zone    America/Chicago      -5:50:36  -      LMT     1883 Nov 18 12:09:24
                             -6:00     US     C%sT    1920

This is a partial excerpt. You can see the full sources here and here.

According to this data, in 1899 Tegucigalpa was still in it's local mean time, which was measured as GMT-05:48:52 (which it stayed on until 1921). However, Chicago had already switched to GMT-06:00 (in 1883). Since 06:00:00 - 05:48:52 = 00:11:08, that is the source of the discrepancy here.

The solution is simple. Set a date and time in your cell instead of just a time. You can still format it as a time-only field if that what's you need to display.

  • This is a great answer. I've added date/time formatting to my test cases to expose the use of December 30, 1899. Now I want to look a lot more closely at how Sheets does time value conversion between sheets having different timezones, and how this has changed between Old and New Sheets. – MetaEd Jun 25 '14 at 15:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.