Utilities.formatDate() in Google Apps Script outputs previous date (e.g. input 25.05.2015 -> output 24.05.2015)

Question

I have a problem with Google Docs' Utilities.formatDate() function.

I have a spreadsheet that contains all of the orders we place in the lab. When an order is delivered our lab manager enters the delivery date in the relevant cell in such a spreadsheet, in the following format: dd.MM.yyyy.

I created a script that, provided certain conditions, will email whoever placed that order alerting them that the order has been delivered on that particular date. Here is the code:

function DeliveryAlerts() {

  try {    
  var email_dict = {"Y":"[email protected]"}
  var spreadsheet = SpreadsheetApp.openById("ABC");
  SpreadsheetApp.setActiveSpreadsheet(spreadsheet); 
  var sheet = spreadsheet.getSheetByName("Orders");
  var values = sheet.getRange("A2:Q251").getValues();
  var bgcolours = sheet.getRange("A2:Q251").getBackgrounds();

  for(var i=0;i<=249;i++) 
    {
      var j = i + 2;
      if (values[i][16]=="Yes" && values[i][11]!="" && bgcolours[i][16]!="#b8b8b8")
        {
         var email_address = email_dict[values[i][13]];
         var cur_date = Utilities.formatDate(values[i][11], "GMT+1", "EEE dd.MM.yyyy");
         var message = "Hello there,\n\nYour order of " + values[i][4] + " has been delivered on "+ cur_date +".\n\nBest wishes";
         var subject = "Delivery alert";
         MailApp.sendEmail(email_address, subject, message,{replyTo:"[email protected]", name:"ABC"});
         sheet.getRange("Q"+j).setBackground("#b8b8b8");
        }
    }  
  } catch (err) {
      MailApp.sendEmail("[email protected]", "Delivery Alerts Script in Order Master List", err);
    }
}

I use Utilities.formatDate(values[i][11], "GMT+1", "EEE dd.MM.yyyy") to reformat the date from, say, 25.05.2015 (that is, the value in the cell) to Mon 25.05.2015. However, what I get instead is Sun 24.05.2015.

Does anybody know what is going on?

Thank you in advance.

Nicola

Are any time zones different for people using the spreadsheet? — Alan Wells

Alan Wells Alan Wells · Accepted Answer · 2015-05-28T14:18:49

Check the time zone setting in the script editor. Under the FILE menu, choose PROJECT PROPERTIES in the script editor. It's possible to have a different time zone setting in Apps Script, than is in the spreadsheet. This is a common issue that arises. Apps Script allows a separate time zone setting from the spreadsheet. Also, even if the time is only off by one minute, if the time setting of the date is all zeros, it's common to get the problem that you are having. When a user enters a date, it's possible that no time setting is made. So the time is set to all zeros. The date is correct, but the time is all zeros. Even if the date was typed in at 3 in the afternoon, for example, and the date is correct, the time setting can be midnight of that day. So, even if you subtracted one second from that date, it would now be the day before.

Utilities.formatDate() in Google Apps Script outputs previous date (e.g. input 25.05.2015 -> output 24.05.2015)

1 Answers