Date Format Issue in Google Sheets Script

Other Questions Google docs
1

I’m having trouble with a Google Sheets script. It’s supposed to turn an ‘x’ in column H into today’s date. But after a few days the dates change into weird numbers like 40492 or 40511.

Here’s what the script does:

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

Any idea why this is happening? Is there something wrong with my code? I’ve tried tweaking it but can’t figure out the problem. Help would be awesome!

2

hey man, i think i kno whats up. those weird numbers are actually excel’s way of storing dates. its counting days since like 1900 or smthing. to fix it, try using the =TODAY() function in ur sheet instead of the script. it’ll update automatically and stay as a real date. hope that helps!

3

I encountered a similar issue in one of my projects. The problem stems from Google Sheets interpreting the date string as a different format than intended. To resolve this, you can use the Utilities.formatDate() function, which allows you to specify the exact format you want.

Here’s a modified version of your script that should work consistently:

function updateCell(event) {
  var sheet = event.source.getActiveSheet();
  var cell = event.source.getActiveRange();
  
  if (cell.getColumn() == 8 && cell.getValue() == 'x') {
    var date = new Date();
    var formattedDate = Utilities.formatDate(date, Session.getScriptTimeZone(), 'MM/dd/yyyy');
    cell.setValue(formattedDate);
  }
}

This approach ensures that the date is formatted correctly before being set in the cell, preventing any unintended conversions by Sheets. Make sure to test this out and let us know if it resolves your issue.

4

I’ve run into this issue before with Google Sheets scripts. The problem is likely due to how Sheets internally stores dates as serial numbers. When you use toLocaleDateString(), it initially appears correct, but Sheets later interprets it as a text string and converts it to its numerical representation.

A more reliable approach is to use the built-in Sheets date functions. Try modifying your script like this:

function updateCell(event) {
  var sheet = event.source.getActiveSheet();
  var cell = event.source.getActiveRange();
  
  if (cell.getColumn() == 8 && cell.getValue() == 'x') {
    cell.setNumberFormat('MM/dd/yyyy');
    cell.setValue(new Date());  // Just use new Date() directly
  }
}

This should solve the date format issue. The setValue(new Date()) will use Sheets’ native date handling, which should maintain the correct format over time. Let me know if this works for you!