I have a header with "Status" and a simple dropdown that is Open, Closed. I am trying to set the next header "Date Completed" with today's date. Not sure where to look at establishing this kind of process. https://docs.google.com/spreadsheets/d/12PNvyd9lAvrR1lg2pyMsy-CkJ-cgEu8y49xv9xIVyd4/edit?usp=sharing

  • 1
    Does this answer your question? Insert current date to cell based on content in other cell
    – Tedinoz
    Aug 16 '20 at 5:01
  • Welcome. Your scenario is not clear. Do you mean: when the dropdown value for a given row = "Completed", then you want the current date to be entered in Column B on that row. This requires an OnEdit(e) script. There are many (many, many) precedents for this. I have listed one here already.
    – Tedinoz
    Aug 16 '20 at 5:04
  • "I am trying to set the next header "Date Completed" with today's date." Just to clarify something here for posterity... the date, when it is entered, will be "today's date". Over time, you want the date to remain fixed on the day that it was entered so that you have an accurate historical record of "when" the task was completed. This is the reason that 'today()' isn't helpful because the date would always change to "today's date", and there would be no record of the actual date that the task was completed. This is why you need a script like the one mentioned above.
    – Tedinoz
    Aug 18 '20 at 4:36
  • I wasn't able to figure out how to read “Closed” and then set todays date.
    – Merkd
    Aug 19 '20 at 5:51

This is an example of a script that watches for an edit to cell. The script is written as a "Simple" onEdit trigger

If the edit is "Closed", then the script inserts the date into the cell adjacent to the edited cell.

The script uses Event Objects to get the range and the value of the edited cell.

The desired value ("Closed") of the edited cell is described as a variable.

An If statement tests whether the edited value is equal to "Closed"; if this is true, then the script inserts the current date into the cell on the same row, one column to the right of the edited cell. The script uses offset(Row,Column) doc ref to define the new range.

The script includes two options for the date.

  • Option#1 (currently remmed out): inserts inserts the date; this means that you should format the cell/column to whatever format you require.
  • Option#2 formats the date within the code so that the date inserted into the cell is preformatted. You can edit the code to format the data in whatever way suits you.

function onEdit(e) {
  var evalValue = "Closed";
  var range = e.range;
  var editedvalue = e.value;
  if (editedvalue === evalValue){
    // the edited cell is equal to "Closed".
    // so make cell on same row and one cell to right = date.
    // Option#1 - format the cell/column however you like it
    // range.offset(0,1).setValue(new Date());
    // Option#2 - set the format in the code; you can additional formatting this this setting
    // refer "formatDate(date, timeZone, format)"
    // https://developers.google.com/apps-script/reference/utilities/utilities#formatdatedate,-timezone,-format
    var formattedDate = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd HH:mm");

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.