How to add current date to cell if the rest of the cells (or some cells) in that row are edited, but only the first time?

Question

I have a column A4:A (heading = Date), and other columns after it B4:O.

I want column A to fill in today's date automatically when I fill in any other columns (some of these B4:O maybe empty, but most will be filled) in that row. But I only want that to happen the first time I edit any other columns within that row.

What I've tried:

A7: =IF(B7 > 0,TODAY(),)

Answer 1

This question is similar to Is there any way to create a timestamp in a Google Spreadsheet? except here you want to date of the first edit, not the most recent one.

• This cannot be achieved with spreadsheet formulas, because if a formula inserts today's date subject to some condition (nonempty cells), it will also insert tomorrow's date tomorrow, if the condition still holds.

• This can be done manually, by pressing Ctrl; (Control/Command and semicolon) in Column A after making the first edit to a row.

• Or this can be done automatically by a script.

Such as this one, which inserts the date of first edit to a row in the first column of that row. The date is inserted in ISO format yyyy-MM-dd. (It can be any other format supported by SimpleDateFormat.)

function onEdit(e) {
  var row = e.range.getRow();
  var dateCell = e.range.getSheet().getRange(row, 1);
  if (!dateCell.getValue()) {
    dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");
  }
}

The script should be entered in Tools > Script Editor. It runs automatically on every edit, but it will not replace the date in column A if it's already there.

To have a script recording the date of last edit, change it

function onEdit(e) {
  var row = e.range.getRow();
  var dateCell = e.range.getSheet().getRange(row, 1);
  dateCell.setValue(new Date()).setNumberFormat("yyyy-MM-dd");
}

Answer 2

In cells from column A you type:

A2: =IF(ISBLANK(B2),"",NOW())

You must format column like date