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();

  for (var i = 0; i < values.length; i++) {

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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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