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
    Commented May 22, 2019 at 8:06
  • frozen, to represent the date the check box was checked. Commented May 22, 2019 at 8:10
  • The date needs to reflect the date that the tick box is checked Commented May 22, 2019 at 8:16
  • for that you will need a script
    – user0
    Commented May 22, 2019 at 8:35

3 Answers 3


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

  • 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. Commented May 22, 2019 at 9:45
  • @ChristopherGrant done. if you tick the checkbox in E7, B5 gets populated with a date-timestamp
    – user0
    Commented May 22, 2019 at 11:14

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.