2

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(),)
3

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");
}
-2

In cells from column A you type:

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

You must format column like date

  • 1
    This will keep the column A changing on every edit, it does not record the time a particular row was edited. – user79865 Feb 17 '16 at 21:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.