Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

Doc1
Locale: United States
Time zone: (GMT-06:00) Central Time
Sheet1
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.

Doc2
Locale: United States
Time zone: (GMT-06:00) Central Time - Tegucigalpa
Sheet1
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

share|improve this question
    
I'm not certain I am giving this a fair test but seem to get 12:00:00 in both sheets myself - is this a continuing difference/problem for you? –  pnuts Apr 11 at 22:52
1  
@pnuts It is still reproducible in Old Sheets. But it is not reproducible in New Sheets. I will update the question to reflect. –  MετάEd Jun 25 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 Jun 25 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 –  MετάEd Jun 25 at 15:28
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. –  MετάEd Jun 25 at 19:55
show 2 more comments

1 Answer

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.

share|improve this answer
    
Thank you. Fascinating answer. WA's equivalent of SO's? (Though same would seem to apply to the new Google Sheets in that case.) –  pnuts Jun 25 at 14:29
    
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. –  MετάEd Jun 25 at 15:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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