I have a datetime columns in Google Spreadsheet which I'd like to iterate and use "hour" value to calculate something in custom Google Script. This is an example of a value I have in Spreadsheet:

enter image description here

My script looks like this:

function CALC_TEST() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("jan");
  var range = sheet.getRange("E3:F14");
  var values = range.getValues();


If I take a look at my logs, the value is interpreted in a really strange way - as Sat Dec 30 23:28:00 GMT+00:00 1899

enter image description here

My Spreadsheet's timezone is set to GMT+00:00 and I get the same result no matter if I try to parse the value with getHours() or getUTCHours() in my Script (obviously, since the value is already wrong before parsing :)).

Why is this happening? How is it possible that 22:00 becomes 23:28 all the sudden?

  • It's U.S. English – errata Feb 23 '17 at 0:00
  • Hm, I find this very strange, but switching TZ and then switching back actually did help :) Wanna put this as an answer so I can accept it? – errata Feb 23 '17 at 9:53
  • 1
    Alright, will do, thanks for all the help!! – errata Feb 23 '17 at 10:00
  • Sure thing! I was suspicious about the time zone settings, but I thought that if it is related to that, I would see just different hours, not the minutes too :) Let me try to find some links related to this bug and I'll answer. – errata Feb 23 '17 at 10:04
  • Hahahaa amazingly fun times! :) I'm glad that I live in a non-daylight saving country although it obviously doesn't make my life easier when dealing with this stuff :) Thanks for the link! – errata Feb 23 '17 at 10:54

Switching the timezone to a different one and then switching back fixed the problem. Might be related to one of the bugs already reported to Google.

Your Answer

