How do you get current date to be added to a cell when a check box is checked in another cell

Question

I want to get the current date added into a cell when a tick box is checked in another cell.

I currently have another formula in the date cell which changes its colour when the tick box is checked, but I also want the date automatically added at the same time as it changing colour.

The colour change is working using this formula, =COUNTIF(F12,"=Y") I want the cell to automatically add the date too. Can anyone help with the additional formula and how do I combine them both?

Answer 1

I adapted user0's answer to have the following additional features:

1. It works in a multi-worksheet spreadsheet by specifying which sheet the change will occur in, as opposed to making the same change in every sheet.
2. It only sets the date if the checkbox is checked (i.e., its value is set to TRUE).
3. I got rid of the date formatting so that it will retain the format of the cell it's in.
4. If the checkbox has any other value than TRUE, it sets the date cell to blank. So if someone checks, unchecks, and then rechecks, only the last date it was checked will be in the date column.

The code is as follows:

function onEdit(e) {
  var activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() == "Data") { // Put the name of the specific worksheet between the quotation marks
    var aCell = e.source.getActiveCell(), col = aCell.getColumn();
    if (col == 2) { // Number of the column with checkboxes
      var dateCell = aCell.offset(0,4); // In offset, put number of rows away (- is up, + is down) and number of columns away (- is left, + is right)
      if (aCell.getValue() === true) {
        var newDate = new Date();
        dateCell.setValue(newDate);
      } else {
        dateCell.setValue("");
      }
    }
  }
}

Answer 2

• you will need a script like this:

function onEdit(e) {
  var aCell = e.source.getActiveCell(), col = aCell.getColumn(); 
  if(col == 5) { //number of column where you have a checkbox
    var adjacentCell = aCell.offset(-2,-3);
    var newDate = Utilities.formatDate(new Date(), 
      "GMT+1", "dd/MM/yyyy hh:mm:ss");
    adjacentCell.setValue(newDate);
  }}

Answer 3

Both of the solutions didn't work for me so I am sharing the solution which had worked for me.

function currentDay(e) {
  if (e == true){
    var da = new Date()
    return da
  }
  return null
}

it works for the alternate cells if you want more complex solutions you can always use the other solutions.