Issue with date formatting and time zone change

Question

I am having a weird issue with dates formatting in Google Sheets and Google Script.

I am developing a script that reads a list of dates in a Google sheet and then sends automatic emails to a list of recipients. I have a cell in the spreadsheet with a date formatted as "dd/mm/yyyy" and then I use the following formulas (cell + 7, cell + 14, cell + 21, cell + 28, etc...) to generate all the other dates in the spreadsheet.

I use the following code to read the dates in the spreadsheet:

var sheet = SpreadsheetApp.getActiveSheet();
var drng = sheet.getDataRange();
var rng = sheet.getRange(4, 2, drng.getLastRow()-3, drng.getLastColumn());
var rngData = rng.getValues();

  
  var todayDate = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy");

  // CYCLE THROUGH DATA
  
  for (var i = 0; i < rngData.length; i++) {   // cycle through the rows in the spreadsheet
  
    var ptLastName = rngData[i][0];     // fixed patient data used to email customization
    var ptFirstName = rngData[i][1];
    var ptDoB = Utilities.formatDate(rngData[i][2], "GMT+1", "dd/MM/yyyy");
    var ptSex = rngData[i][3];
    var ptEmail = rngData[i][4];
    var ptDoS = Utilities.formatDate(rngData[i][5], "GMT+1", "dd/MM/yyyy");
    var ptDoD = Utilities.formatDate(rngData[i][6], "GMT+1", "dd/MM/yyyy");

    for (var j = 7; j <= 14; j++) {   //  cycle through follow-up data for the current patient
      
      var currentDate = Utilities.formatDate(rngData[i][j], "GMT+1", "dd/MM/yyyy");
      
      if (currentDate == todayDate) {   // send email with the questionnaire
        
         writeEmail(ptFirstName, ptLastName, ptSex, ptDoD, ptEmail);
         rng.getCell(i+1, j+1).setBackground("yellow");
      }
      
      
    }
    
  }

The issue is that when I try for read and format some of the dates they are not corresponding to the ones listed in the spreadsheet. I have noted that the wrong dates in the code are systematically 1 day earlier than the ones listed in the spreadsheet. On a closer look I have noted that some of the dates read from the spreadsheet at GMT+2 while others are GMT+1.

That's weird because I have checked my system settings (it GMT+1) as well as the spreadsheet settings (GMT+1). So I don't understand why some dates are automatically converted to GMT+2.

This might be related to daylight saving timezones. Add some sample data input and the expected results. — Rubén
Related Google date on spreadsheet is different than date in script logger. Is this a time zone issue? — Rubén

Cooper Cooper · Accepted Answer · 2020-08-01T21:00:09

I tried it this way and it works okay:

function dattest() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getDataRange();
  const vs=rg.getValues()[0];
  vs.forEach(function(d){
    Logger.log(Utilities.formatDate(new Date(d),Session.getScriptTimeZone(),"dd/MM/yyyy"));
  });
}

Display Values:

8/1/2020,8/8/2020,8/15/2020,8/22/2020

Formulas:

,=A1+7,=A1+14,=a1+21

The output from view Executions:

Aug 1, 2020, 2:52:37 PM Info    01/08/2020
Aug 1, 2020, 2:52:37 PM Info    08/08/2020
Aug 1, 2020, 2:52:37 PM Info    15/08/2020
Aug 1, 2020, 2:52:37 PM Info    22/08/2020

Seems correct to me

Issue with date formatting and time zone change

2 Answers