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

We will have a new build out shortly that hopefully fixes the issue. If not, we will have to do something a little more drastic. ;-)

Stay with us, we will have a new build shortly. You can re-submit with the new build and tell us if Apple is still cranky.

Also, please feel free to copy the review board with this thread, as the GMS* code they are getting upset about is our code, not yours.

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