1

I have a Google App script that reads a row with two dates from a Spreadsheet.

The two dates are read with different timezones and I do not understand why.

Cell contents are:

25/12/1941  16/02/2021

Dates are read through

  var dataRng = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Elenco').getRange("G"+rowElenco+":AC"+rowElenco);
  var dati = dataRng.getValues();

First date is read as

Thu Dec 25 1941 00:00:00 GMT+0200 (Central European Standard Time)

Second date is

Tue Feb 16 2021 00:00:00 GMT+0100 (Central European Standard Time)

Why the first date is GMT+"?

I (think I) have properly setup the SpreadSheet and Script timezones:

  Logger.log(Session.getScriptTimeZone()); 
  Logger.log(SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone());

outputs:

03:49:18    Informazioni    Europe/Rome
03:49:18    Informazioni    Europe/Rome

This is causing issues because I try to format the date and

var dtNascita = Utilities.formatDate(dati[0][11], "GMT+1", "dd/MM/yy");

becomes

dtNascita: "24/12/41"

What am I missing?

dan
  • 13
  • 2
  • Likely a historic timezone offset issue. Around the early 1940's many European countries changed their offsets to effectively have daylight saving all year round. E.g. Britain had double daylight saving (+2). See [*Browsers, time zones, Chrome 67 Error (historic timezone changes)*](https://stackoverflow.com/questions/50609860/browsers-time-zones-chrome-67-error-historic-timezone-changes). – RobG Mar 21 '21 at 08:07
  • E.g. `new Date(Date.UTC(1941,0)).toLocaleString('en-GB',{timeZone:'Europe/Rome', timeZoneName:'short'})` gives "01/01/1941, 2:00:00 GMT+2", whereas the same date for 2021 gives "01/01/2021, 1:00:00 CET" (i.e. GMT +1). – RobG Mar 21 '21 at 08:13

1 Answers1

1

You have to set the timezone when converting the date format in order to always have the same value to consider:

var ss = SpreadsheetApp.getActiveSpreadsheet();
Utilities.formatDate(dati[0][11], ss.getSpreadsheetTimeZone(), "dd/MM/yy");

https://www.analyticstraps.com/utc-vs-gmt-vs-getspreadsheettimezone/

Michele Pisani
  • 13,567
  • 3
  • 25
  • 42
  • While I don't fully get why... It works like a charm. Also works with "Europe/Rome" instead of "GMT+1". Grazie! – dan Mar 22 '21 at 00:42