How to automatically input time & date in google sheets-CodePudding

I need some help. I want Rows B & C to automatically fill in time and date respectively when I input data in D. I would also like for Row I to automatically add time when I input data into Row H.

I've tried ARRAYFORMULA IF with NOW and TODAY but time resets every time I input a value below the column, and date resets every time I open the sheet the next day. Any insight would be helpful!

Here is how my sheet looks like: Sample

CodePudding user response：

Put this script in your script editor, change sheetName if any, save, and try within your sheet, set format of columns B, C and I as you wish

function onEdit(event){ 
  var sheetName = "mySheet";
  var sh = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if ((r.getColumn() == 4) && (sh.getName() == sheetName)){ 
    sh.getRange('B' r.getRow()).setValue(new Date());
    sh.getRange('C' r.getRow()).setValue(new Date());
  }
  if ((r.getColumn() == 8) && (sh.getName() == sheetName)){ 
    sh.getRange('I' r.getRow()).setValue(new Date());
  }
}

you may probably to execute once in your script editor to give authorization (even if you get an error)

CodePudding user response：

Description

You could use a simple onEdit(e) trigger. The script below is tested using Sheet1.

A minor twist on Mike's answer in that the number format is set too.

Script

function onEdit(e) {
  try {
    var now = new Date();
    var range = null;
    // make sure the edit occured on the right sheet
    if( e.range.getSheet().getName() === "Sheet1") {
      // make sure the edit occured in column D
      if( e.range.getColumn() === 4 ) {
        range = e.range.offset(0,-3);
        range.setValue(now);
        range.setNumberFormat("m/d/yyy");
        range = e.range.offset(0,-2);
        range.setValue(now);
        range.setNumberFormat("h:mm:ss am/pm");
      }
      // or make sure the edit occured in column I
      else if( e.range.getColumn() === 9 ) {
        range = e.range.offset(0,-1);
        range.setValue(now);
        range.setNumberFormat("h:mm:ss am/pm");
      }
    }
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Reference