1

Using today(), I have a cell populate with the current date when an adjacent cell is filled. Unfortunately, when tomorrow rolls around, yesterday's date, from tomorrow's perspective, will be the wrong date.

Is there a way to freeze today() as the day it was written and not the current date?

Note: there are thousands of these cells, so doing Cmd + ; does not solve this issue. It needs to be a formula that can be copied to those thousands of cells.

player0
  • 124,011
  • 12
  • 67
  • 124
h.and.h
  • 690
  • 1
  • 8
  • 26

1 Answers1

3

possible only with script:

function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet3" ) { 
var r = s.getActiveCell();
if( r.getColumn() == 13 ) { 
  var nextCell = r.offset(0, 9);
  var newDate = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy hh:mm:ss");
  nextCell.setValue(newDate);
}}}
  • "Sheet3" = sheet name
  • r.getColumn() == 13 = column M / 13th column
  • r.offset(0, 9) = offset timestamp 9 columns to the right on the same row eg column V
  • "GMT+8" = timezone
  • "MM/dd/yyyy hh:mm:ss" = date and time format
player0
  • 124,011
  • 12
  • 67
  • 124