Spreadsheet Date Format Issue in Google Docs

Other Questions Google docs
1

I’m having a weird problem with dates in my Google Docs spreadsheet. I’ve got a script that turns an ‘x’ in column H into today’s date. But after a few days, all the dates turn into numbers like 40492, 40494, and 40511.

I’m not sure what’s causing this. Maybe there’s a problem with my script? Here’s a simplified version of what I’m using:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  
  if (cell.getColumn() == 8 && cell.getValue() == 'x') {
    cell.setNumberFormat('MM/dd/yyyy');
    cell.setValue(new Date());
  }
}

Has anyone seen this before? Any ideas on how to keep the dates from changing to numbers? I’m pretty new to Google Sheets scripting, so I’m not sure what I’m doing wrong. Thanks for any help!

2

hey alexj, ive seen this before. those numbers are actually excel’s date format. google sheets sometimes messes up and shows dates like that. try changing the cell format manually to date, it might fix it. if not, maybe add a line to ur script to force date format everytime?

3

I’ve encountered this issue before, and it can be quite frustrating. The problem lies in how Google Sheets handles date formatting internally. Those numbers you’re seeing are actually serial numbers representing dates.

One solution I’ve found effective is to use the TEXT function in combination with your script. Instead of setting the date directly, you could modify your script to insert a formula:

function onEdit(e) {
var sheet = e.source.getActiveSheet();
var cell = e.range;

if (cell.getColumn() == 8 && cell.getValue() == ‘x’) {
cell.setFormula(‘=TEXT(TODAY(), “MM/dd/yyyy”)’);
}
}

This approach ensures the date always displays correctly, even if the underlying format changes. It’s been a reliable workaround in my experience, especially when dealing with scripts that manipulate dates frequently.

4

This issue stems from how Google Sheets internally stores dates. Those numbers represent days since December 30, 1899. To resolve this, you could modify your script to apply a specific date format each time:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var cell = e.range;
  
  if (cell.getColumn() == 8 && cell.getValue() == 'x') {
    var today = new Date();
    cell.setValue(today);
    cell.setNumberFormat('MM/dd/yyyy');
  }
}

This should consistently display dates in the desired format. Additionally, you might want to check your spreadsheet’s locale settings, as they can affect date interpretation.