1

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?

  • do you want that date to stay frozen or change every day? – user0 May 22 at 8:06
  • frozen, to represent the date the check box was checked. – Christopher Grant May 22 at 8:10
  • The date needs to reflect the date that the tick box is checked – Christopher Grant May 22 at 8:16
  • for that you will need a script – user0 May 22 at 8:35
0
  • 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);
  }}

  • Thanks for the help, sorry I am a complete novice. If my check box is in cell E7 and the cell I want the date in is B5 how would this formula look? Sorry for being so thick! Could you edit it for me to show what it should look like for this example please. Many thanks for the help. – Christopher Grant May 22 at 9:45
  • @ChristopherGrant done. if you tick the checkbox in E7, B5 gets populated with a date-timestamp – user0 May 22 at 11:14
0

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

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.