An “Updated on” field that records the last time that a particular column changed [duplicate]

Question

This question already has an answer here:

• Auto-updating column in Google Spreadsheet showing last modify date 3 answers

Is it possible to create a "last updated" field under Google Sheets?

I have a status column, where the status progresses something like:

New -> Pending -> Started -> Done

I would like to add a "status last changed on XXX" column, where XXX would be a timestamp for when the status was changed, so that then I can filter for example anything that hasn't changed status in over a week (or color-code it/etc.)

I know I can in theory look this up under the revision history, but that is quite a tedious work-around

I am looking for a way that if a single cell is modified, then another specific cell will be updated with the current timestamp

Answer 1

Here is an easily customizable function for this purpose: enter the numbers of the column to watch and the column for timestamp on appropriate lines

function onEdit(e) {
  var colWatch = 5;      // column to watch, 5 = "E"
  var colRecord = 6;     // column for timestamp,  6 = "F"
  if (e.range.getColumn() == colWatch) {
    e.range.offset(0, colRecord - colWatch).setValue(new Date());
  }
}