Google Sheets Date Format Converting to Numbers

Other Questions Google docs
1

I have multiple scripts running on my Google Sheets document. When I enter a letter ‘x’ in column J (which is column 10), my script automatically converts it to today’s date.

However, after several days pass, all the dates in column J transform from proper date format into plain numbers like: 44521, 44523, 44540.

I can’t figure out what’s causing this issue. It might be related to one of my scripts. Here are the three scripts I’m using:

Script One:

function onEdit(e) {
  var highlightA = "lightblue";
  var highlightB = "#eeeeee";
  var highlightC = "#eeeeee";
  var worksheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Order Tracking");
  var activeRange = e.source.getActiveRange();
  var currentSheet = SpreadsheetApp.getActiveSheet().getName();

  if (currentSheet == "Order Tracking") {
    if (activeRange.getColumn() == 9 && activeRange.getValue() != "") {
      var rowNum = activeRange.getRow() + 1;
      worksheet.getRange("A" + rowNum + ":I" + rowNum).setBackgroundColor(highlightC);
    }
  }

  var columnNum = e.source.getActiveRange().getColumn();
  if(columnNum == 10 || columnNum == 9) {
    var totalRows = worksheet.getMaxRows();
    var rangeJ = worksheet.getRange("J1:J"+totalRows);
    var valuesJ = rangeJ.getValues();
    var rangeI = worksheet.getRange("I1:I"+totalRows);
    var backgroundI = rangeI.getBackgroundColors();
    var valuesI = rangeI.getValues();

    for (var i = 0; i < valuesJ.length; i++) {
      var currentRow = backgroundI[i];
      if (valuesJ[i][0] != "" && valuesI[i][0] == "") {
        currentRow[0] = highlightA;
      } else if (valuesI[i][0] != "") {
        currentRow[0] = highlightB;
      }
    }
    worksheet.getRange("I1:I" + totalRows).setBackgroundColors(backgroundI);
  }
}

Script Two:

function onEdit(e) {
  var colorX = "lightblue";
  var colorY = "#eeeeee";
  var colorZ = "#eeeeee";
  var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Order Tracking");
  var selectedRange = e.source.getActiveRange();
  var sheetTitle = SpreadsheetApp.getActiveSheet().getName();

  if (sheetTitle == "Order Tracking") {
    if (selectedRange.getColumn() == 5 && selectedRange.getValue() != "") {
      mySheet.insertRowAfter(selectedRange.getRow());
      var newRow = selectedRange.getRow() + 1;
      mySheet.getRange("A" + newRow + ":J" + newRow).setBackgroundColor(colorZ);
    }
  }

  var colIndex = e.source.getActiveRange().getColumn();
  if(colIndex == 5 || colIndex == 10) {
    var maxRows = mySheet.getMaxRows();
    var columnE = mySheet.getRange("E1:E"+maxRows);
    var dataE = columnE.getValues();
    var columnJ = mySheet.getRange("J1:J"+maxRows);
    var colorsJ = columnJ.getBackgroundColors();
    var dataJ = columnJ.getValues();

    for (var j = 0; j < dataE.length; j++) {
      var rowColor = colorsJ[j];
      if (dataE[j][0] != "" && dataJ[j][0] == "") {
        rowColor[0] = colorX;
      } else if (dataJ[j][0] != "") {
        rowColor[0] = colorY;
      }
    }
    mySheet.getRange("J1:J" + maxRows).setBackgroundColors(colorsJ);
  }
}

Script Three:

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  var cellRange = e.source.getActiveRange();
  if (cellRange.getColumn() == 10 && cellRange.getValue() == "x") {
    cellRange.setNumberFormat("dd/MM/yyyy")
    cellRange.setValue(Utilities.formatDate(new Date(), "EST", "MM-dd-yyyy"));
  }
}

Can anyone help me understand why the dates keep turning into numbers?

2

This happens because Google Sheets stores dates as serial numbers internally - those numbers you’re seeing are days since December 30, 1899. Your scripts mess with column J using getValues() and setBackgroundColors(), which pulls the raw numeric data. When you do range operations after that, Sheets loses track of the original formatting. I had the same problem with my inventory sheet where dates would randomly turn into numbers after running scripts. Here’s the fix: explicitly preserve the number format during your range operations. After each setBackgroundColors() call in your first two scripts, add this line to reapply the date format: worksheet.getRange("J1:J" + totalRows).setNumberFormat("dd/MM/yyyy"). This keeps the proper date display even after the background color operations run.

3

classic sheets prob! those numbers r actual date values - sheets stopped showing them as dates. your scripts keep readin/writin column J without keepin the format. add .setNumberFormat("dd/MM/yyyy") after ur bg color stuff in scripts 1&2. every time you touch that range, it resets the formatting to auto.

4

I’ve encountered this exact problem, and it can be quite frustrating! It appears that your scripts are repeatedly processing the entire range of column J, which interferes with Google Sheets’ formatting. Each time they execute getValues() and setBackgroundColors() on that entire column, they are essentially “touching” every cell in column J, resetting the number format back to the default. This is why Google Sheets views your dates as serial numbers again since those constant operations are stripping away the formatting from Script Three. I managed to resolve this in my own sheet by ensuring the number format is retained whenever the background color changes. You can simply add rangeJ.setNumberFormat("dd/MM/yyyy") right after you set the background colors in Script One and Script Two. This way, the date format stays consistent even after the range operations are complete.