Google Docs Date Changing

I have three scripts that are in a google docs spreadsheet. In this spreadsheet, in column H (or column 8), if I type an x, the script changes it into that days date.

After a few days, every date in column H has changed from a date to just a number. The numbers look like this: 40492, 40494, 40511.

I am not sure what is causing this. Maybe it's something that is wrong in my script. I've pasted them below. Any ideas?

Here's the first one:

function onEdit(e) {

  var colorA = "yellow";
  var colorB = "#dddddd";
  var colorC = "#dddddd";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchase Orders");
  var range = e.source.getActiveRange();
  var sheetName = SpreadsheetApp.getActiveSheet().getName();

  if (sheetName == "Purchase Orders") {

 // 3 is column C
    if (range.getColumn() == 7 && range.getValue() != "") {
      var r = range.getRow() + 1;
      sheet.getRange("A" + r + ":G" + r).setBackgroundColor(colorC);
    }
  }

    var col = e.source.getActiveRange().getColumn();
    if(col == 8 || col == 7) {
    var rows = sheet.getMaxRows();

    //column C
    var rangeC = sheet.getRange("H1:H"+rows);
    var valuesC = rangeC.getValues();
    //column H range
    var rangeH = sheet.getRange("G1:G"+rows);
    var colorH = rangeH.getBackgroundColors();
    var valuesH = rangeH.getValues();

    //iterate over each row in column C and H
    //then change color
        for (var row = 0; row < valuesC.length; row++) {
          //check for columnC and column H
          var hRow = colorH[row];
          if (valuesC[row][0] != "" && valuesH[row][0] == "") {
            hRow[0] = colorA;
          } else if (valuesH[row][0] != "") {
            hRow[0] = colorB;
      }
        }
    sheet.getRange("G1:G" + rows).setBackgroundColors(colorH);




}
  }

Here's the second one:

function onEdit(e) {

  var colorA = "yellow";
  var colorB = "#dddddd";
  var colorC = "#dddddd";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchase Orders");
  var range = e.source.getActiveRange();
  var sheetName = SpreadsheetApp.getActiveSheet().getName();

  if (sheetName == "Purchase Orders") {

 // 3 is column C
    if (range.getColumn() == 3 && range.getValue() != "") {
      sheet.insertRowAfter(range.getRow());
      var r = range.getRow() + 1;
      sheet.getRange("A" + r + ":H" + r).setBackgroundColor(colorC);
    }
  }

    var col = e.source.getActiveRange().getColumn();
    if(col == 3 || col == 8) {
    var rows = sheet.getMaxRows();

    //column C
    var rangeC = sheet.getRange("开发者_如何学PythonC1:C"+rows);
    var valuesC = rangeC.getValues();
    //column H range
    var rangeH = sheet.getRange("H1:H"+rows);
    var colorH = rangeH.getBackgroundColors();
    var valuesH = rangeH.getValues();

    //iterate over each row in column C and H
    //then change color
        for (var row = 0; row < valuesC.length; row++) {
          //check for columnC and column H
          var hRow = colorH[row];
          if (valuesC[row][0] != "" && valuesH[row][0] == "") {
            hRow[0] = colorA;
          } else if (valuesH[row][0] != "") {
            hRow[0] = colorB;
      }
        }
    sheet.getRange("H1:H" + rows).setBackgroundColors(colorH);




}
  }

Here's the third one:

function onEdit(e) {
  var ss = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
   //1 is A, 2 is B, ... 8 is H
  if (r.getColumn() == 8 && r.getValue() == "x") {
    r.setNumberFormat("MM/dd/yyyy")
    r.setValue(Utilities.formatDate(new Date(), "MST", "yyyy-MM-dd"));

  }  
}  

---

First possibility is that another macro is overwriting the column - worth ruling out. However the most likely problem is that you are not setting the format of the column to be a date format. If it is a general format, then the date is displayed as a number like you are seeing.

The reason is that this is how it is stored, and you are not telling the cells to interpret this number as a date, so it doesn't.

My excel scripting is not quite up to giving you code, but if you make sure you format the colum explicitly to display dates, then you should be OK. The reason it initially looks OK, is because when you add a date it sees it as a date, but if it not saved with defined format, then it will open again with the default.

HTH

---

Following what Schroedinger said, try casting the number format to a date format

Select the range of cells you want to change:

Ex. H24:H28

In the drop-down menu select Format->Number then the date format you want to use. It might or might-not work.

If that doesn't make a difference you will need to cast the integer value in the scripting code to a date format explicitly (the conversion may have been happening implicitly before).

If you don't know how to cast types in JavaScript, it would be worth it to look up or open a new question on this site.

---

In my locale (uk), on a PC keyboard typing Ctrl+; (Ctrl with a semicolon) today's date will insert into the selected cell (in Col H). You could try this rather than your third script.

If you select column H and choose from the menu Format>Number>normal, then the date will be dispayed as a number (as stated by Schroedingers Cat). Conversely, selecting Format>Number>date will put all the numbers back to the date format appropriate to the current Spreadsheet settings.