IssueTracker

Hello Seth,

Would you please provide a code sample and the input string for Utilities.formatDate?

Thanks,
Anton

I have set spreadsheet timezone, script timezone, and tried using both EST and GMT-5 in the Utilities.formatDate() function.   The date returned is always 1 day less than the date acquired from the spreadsheet (the first use of formatDate, acting on "today" works fine).   Here's the function code:

function longRangePlan() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var today = new Date();
  today = Utilities.formatDate(today, "EST", "MM/dd/yyyy");   //Strip off the time values we dont need
  var startRow = 2;                                           // First row of data to process
  var numRows = 82;                                           // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 35);   // Fetch the range of cells A2:AE61
  var data = dataRange.getValues();                           // Fetch values for each row in the Range.
  var fileName = Browser.inputBox("Save long range planning as:");                // Prompts the user for the file name
  if (fileName.length !== 0) {                                // Check that the file name entered wasn't empty


    var docNew = DocumentApp.create(fileName);                  // Create LRP document
    docNew.insertParagraph(0,"Long Range Planning - "+myName+" - "+today).setBold(true);  //Bold the title
    var myTable = docNew.insertTable(1);                           // Insert a table
    var tableRow = myTable.appendTableRow().setBold(true);
    tableRow.appendTableCell("Name");
    tableRow.appendTableCell("Meeting");
    tableRow.appendTableCell("Date");
    tableRow.appendTableCell("Time");  
    for (i in data) {                                          //And prepare to populate the doc
      var row = data[i];
      var day = new Date(row[12]);
      var sixMonth = Utilities.formatDate(day, "EST", "MM/dd/yyyy");          //Strip off the time values we dont need
      var day1 = new Date(row[14]);
      var annualReview = Utilities.formatDate(day1, "EST", "MM/dd/yyyy");     //Strip off the time values we dont need
      var day2 = new Date(row[16]);
      var twentyfourMonth = Utilities.formatDate(day2, "EST", "MM/dd/yyyy");  //Strip off the time values we dont need
      var day3 = new Date(row[18]);
      var TPM = Utilities.formatDate(day3, "EST", "MM/dd/yyyy");              //Strip off the time values we dont need
      if (row[25] >= 0 && row[25] <= 270){                                    //If there's a 6 month between now and 9 months...
        var tableRow = myTable.appendTableRow().setBold(false);               //populate doc
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("6 Month Review");
        tableRow.appendTableCell(sixMonth);
        tableRow.appendTableCell(row[13]);
      }    
      if (row[26] >= 0 && row[26] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("Annual Review");
        tableRow.appendTableCell(annualReview);
        tableRow.appendTableCell(row[15]);
      }  
      if (row[27] >= 0 && row[27] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("24 Month Review");
        tableRow.appendTableCell(twentyfourMonth);
        tableRow.appendTableCell(row[17]);
      }      
      if (row[28] >= 0 && row[28] <= 270){
        var tableRow = myTable.appendTableRow().setBold(false);
        tableRow.appendTableCell(row[5]+", "+row[4]);
        tableRow.appendTableCell("TPM");
        tableRow.appendTableCell(TPM);
        tableRow.appendTableCell(row[19]);
      }  
    }
      for (i in data){
      var row = data[i];
      if (row[12]== "" && row[14] == "" && row[16] =="" && row[18] == "" && row[24]<=1095 && row[23] != 1){   //If things need to be
       docNew.appendParagraph(row[4]+" "+row[5]+" appears to have no meetings scheduled.");                   //scheduled, add info
      }
      if (row[26]<= 0 && row[25] <= 0 && row[25] < row[26] && row[29] >= 180 && row[29]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs a 6 month review scheduled.");
      }
      if (row[25]<= 0 && row[26] <= 0 && row[25] > row[26] && row[30] >= 180 && row[30]<=1094){
        docNew.appendParagraph(row[4]+" "+row[5]+" needs an annual review scheduled.");
      }        
      if (row[27]<=0 && row[24]>=517 && row[24]<=730){
        docNew.appendParagraph(row[4]+" "+row[5]+" needs a 24 month meeting scheduled.");
      }    
      if (row[22] == 0 && row[18] == "" && row[28]<=0 && row[24]>=700 && row[24]<=1095){                                           // row 23 is chron age in days
        docNew.appendParagraph(row[4]+" "+row[5]+" needs a TPM scheduled.");
      }
      if (row[22]==1 && row[24]<=1460 && row[26]<= 0 && row[25] <= 0 && row[25] < row[26]&& row[29] >= -185 && row[29]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs a 6 month review scheduled.");
      }
      if (row[22]==1 && row[24]<=1460 && row[26]<= 0 && row[25] <= 0 && row[25] > row[26]&& row[30] >= -185 && row[30]<=1094){
         docNew.appendParagraph(row[4]+" "+row[5]+" needs an annual review scheduled.");
      }
    }
  }
  else {
    Browser.msgBox("Error: Please enter a valid file name.");                 //If the user didn't put in a filename,
  }                                                                           //throw an exception
  docNew.saveAndClose();
}

Hello Seth,

I have done some testing on your issue.
It appears that the formatDate works as expected BUT
the values are read incorrectly from the spreadsheet.

If your spreadsheet says 1/25/2012, they value will be read as 1/24/2012.

A temporary workaround would be to provide the time as well as the date.
For example, instead of "1/25/2012" you can write "1/25/2012 12:00pm".
If you do this the value will be read correctly and you can use formatDate to get rid of the time.

In the mean time will investigate the reason why this is happening, and provide a fix for it.

Best,
Anton

I appreciate the temporary workaround, but it's not feasible for me.   I have this script distributed across my organization and most of the users are not technically savvy, nor are they going to be understanding of the idea that dates are not correctly supported in spreadsheets.   No offense, but I'm already getting a lot of grief over this issue.

Can you give me some sort of idea of when this might be corrected?

Thank you.

Seth,

Unfortunately I can't give you an ETA yet, but I will try to get more info to you as soon as it is available.

Best,
Anton

Thank you.

Any updates?

Thank you.

Here is a similar Javascript workaround I used. 'toopid Chrome!

var enteredDate = new Date(document.getElementById('date').value + " 12:00:00");

Has this been corrected yet?

I'm doing my best to be patient; however having a spreadsheet program which cannot handle dates correctly is ludicrous.

I cannot, nor should I have to, use any of these workarounds.

Please give an update as to where in the process we are in resolving this problem, even if an ETA is unavailable.

Thank you,
Seth

Seth,

We'll give you more info as soon as it is available.
No updates for now.

Best,
Anton

I just ran into a similar problem.  You might try this:

Utilities.formatDate(today, "EST", "MM/dd/yyyy HH:mm:ss");

To see what you are getting for time.  I found that "EST" subtracted one hour from my stored date value probably in "EDT".  I am using a DateBox to store dates which seems to be storing the time as 00:00 EDT, so it falls back a day to 23:00 EST.  If I use "GMT" it makes the time 04:00 (correct date though).  If I use at least "GMT-4" I should be fine (because I'm only concerned with the date and even with daylight savings the time will not subtract an hour into the previous date).  Since you are stripping the time hopefully it will help you too.

I found using this is sufficient for my needs:
Utilities.formatDate(today, "GMT", "MM/dd/yyyy");

If you are storing time and need that precision you will need to determine exactly what timezone your data is stored in.  I already tried but "EDT" does not seem to be a valid option for the formatdate function, it just gives the same output as "GMT".

Hope that helps!

Thank you for the workaround, but it is not feasible for me to go and edit thousands of spreadsheets using my code.

This issue needs to be resolved!  

Date handling is an absolutely basic function.   My intention is to start to seek out blog posts and other discussions of Google docs and bring this issue to light.   It's one thing to have a bug, it's quite another to allow something as gamebreaking as date mismanagement to continue to exist for two months.

This is not really a bug, but a technique issue. Please clarify the GAS doc's...

Add examples or describe the differences between explicitly specifying the timezone versus timezoneID (Utilities.formatDate seems to except either) when working with dates.

Example 1: Utilities.formatDate(new Date(), "MST", "yyyy-MM-dd");
Example 2: Utilities.formatDate(new Date(), "MDT", "yyyy-MM-dd");
Example 3: Utilities.formatDate(new Date(), "America/Denver", "yyyy-MM-dd");

Only one of the first two examples will work depending on weather it is tested during  Daylight Savings time or not. The third example will work year round.

Another approach is to use (assuming the location and timezone is set in File>Spreadsheet Settings):
  var tz = SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  // Returns timezoneID not timezone as indicated in the docs,
  //but still works even across time change.
  var date_now = Utilities.formatDate(new Date(), tz, "yyyy-MM-dd");

This issue is no longer a concern from my perspective.   We have decided to switch over to a working system.   I am, however, warned now by this experience that Google products may not be implemented robustly enough for non-hobbyist use.

From my perspective, a standard format which is implemented in such a way that it does not perform the way it performs in other languages, including the language from which it was borrowed, is a bug.

This appears to be related to issues 1012 and 1271.  I would classify this as a definite bug - inconsistent results returned by Date(), and by extension dateformat.  In my experience I don't agree that this is technique issue, so I totally support Seth's last few responses - Date() is such a fundamental function that if it doesn't work in the same way as implemented in other languages then it's a bug.

Peter

Any resolution on this?

No resolution that I know of, though I gave up and migrated code to a responsible company.

The workaround that I have implemented is to not use the three-letter time zone codes but rather use the Zone Name used in the TZ database. You can find the list here: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones

For example, instead of "EST", I use "Canada/Eastern"

That worked for me.  Wow.

Shame that Seth couldn't change one line of code and migrated his entire system to a paid service.

Surely he'd have to update the existing incorrect dates anyway since the damage was already done.

It already was a paid service for us.

I would have had to change one line of code in over 1000 spreadsheets since it could not be automatically updated across all of them.

The final analysis was that if Google couldn't manage to get something as simple as dates correct, we decided we could not trust their codebase.

I apologize for the long delay addressing this issue. I did some more investigation, and everything appears to be working as expected. The dates in the spreadsheet are relative to the timezone of the spreadsheet. When accessed by Apps Script, those dates are converted to the timezone of the script. When those dates are converted to a string they are always converted using Pacific Time. To convert them to a string in a specific timezone you can use Utilities.formatDate().

The following snippet of code puts this all together, to get the date as a string in the timezone of the original spreadsheet.

var date = SpreadsheetApp.getActiveRange().getValue();
var timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
var dateString = Utilities.formatDate(date, timezone, 'MM/dd/yyyy');
Browser.msgBox(dateString);

I understand that this behavior can be a bit confusing, and I'll open a new issue for us to better document how timezones work in Apps Script.

Thanks for the eventual response.

I have a hard time with the idea that it is intentional to break a standardized function.   I'm not sure who expects functions to be written in such a way that they deviate from a well established, well documented, historic standard, but I guess for those people this is working as expected.

Apps Script implementation does not conform to the standard set by ECMAScript, nor does Javascript does implement this function this (broken) way.

So not only is Apps Script breaking the standard function from the original specifications, but also not conforming to the way it is implemented in the language Apps Script is supposed to be based on.

If you're choosing to say you're not going to fix it, I have no issue with that, but it's disingenuous to claim it is working correctly.

@Seth, can you provide more detail as to how our implementation differs from the ECMAScript or JavaScript standards. Our goal is to be as compliant as possible with these standards, as it makes the Apps Script platform easier for new developers to work with and easier for our team to support.

One key difference to keep in mind is that unlike a JavaScript running in web page which only must consider the timezone of the browser, in this case Apps Script must consider the timezone of the spreadsheet and script as well.

As a side note, I did some more research and found that if you convert the date to a string before passing it into Logger.log() it will use the script's timezone instead of Pacific Time. For example:

var date = new Date();
Logger.log(date);
Logger.log(String(date));

Logs the following:

Wed Dec 19 21:52:24 PST 2012
Thu Dec 20 2012 00:52:24 GMT-0500 (EST)

Unfortunately the underlying architecture makes it unfeasible to do this automatically, but this is another simple workaround you can use.

@Eric

In Javascript, as per the ECMAscript standard, when you tell the function the number you want put in the day portion of the date:

var day = new Date(row[12]);
var sixMonth = Utilities.formatDate(day, "EST", "MM/dd/yyyy");

It doesn't change that number to a different number.

It's been a year since I reported this, so I frankly don't remember the details, but Apps Script was subtracting 1 from the number I gave it.

If I tell it I want the date to be, for example, January 12th it shouldn't decide to make the date January 11th when I use the exact formatting specified by the documentation of Javascript (which the Google Apps Script documentation points to instead of providing any other information) which is predictably coherent with the original documentation from ECMAScript.

@Seth, I haven't seen any problems with it subtracting one from the days in Date objects. If you can provide more details I'll consider reopening this bug, but as far as I can tell we are not differing from the standards.

That's the issue this entire thread is about.  

1. It's well documented by myself as well as others who have commented both in this thread and other threads, at least one of which was merged into this thread.  

2. I provided code snippet (comment #2) and indicated the resultant behavior.

3. Based on that code the bug was confirmed by Google employee Anton and this issue was listed as triaged (comment 3 - the function formatDate is incorrectly reading dates from spreadsheets).

4. In that same post a fix was promised by Anton.

I'm no longer developing using Apps Script so I'm not prepared to put in a bunch of work.   You have all of the info you need in this thread if you wish to delve deeper into the problem.

I came across this researching an issue I was having with it showing 6 hours ahead of my time.  The code I copied was "GMT+0500" so that was right.  Changing to "GMT-0600" didn't work.  Also tried "GMT-6" "CST"  But what got it to work was "GMT-06:00".  Not sure if this will fix your issue if you can get your GMT time correct.  Just giving some input to maybe help out someone that might have the same problem as me.  

EST and EDT are several hours apart using Utilities.formatDate (checked in Logger.log), not one hour like it should be - this is considered "working as intended" by google?

Tried the suggestion above to hardwire in the name of the timezone: America/New_York in place of EST/EDT and it worked. Too bad the googlers couldn't help with that suggestion as it came from another user.

-signed tired of workarounds for every single google product

 Can you believe it? I spent all day thinking this was me? LOL

Vote up!