Locking or freezing the TODAY() formula

I've looked around in the community about locking or freezing a current date, but I'm still having issues.

That if i have marked tick on cell "C2 then today's date "11-11-2020" will auto populate into cell D2 i.e,. 11-11-2020 and that date will automatically changed to 12-11-2020 on very next day. Because of =TODAYS() function.

My goal is to lock a date that's triggered by marking a tick and have that current date freeze until the tick is removed.

My current sheet below shows the 10-11-2020 date which will change tomorrow and needs to be "locked" once the tick is marked and remain 10-11-2020 until the tick is removed

Could anyone help me correct formula to what it needs to be?

=IF(C2="Checked",TODAY(),"")

It is google sheet.

Thank you for replying promptly.

Actually i have been using this on google sheets.

Originally Posted by shabby01

. . . My goal is to lock a date that's triggered by marking a tick and have that current date freeze until the tick is removed. . . .

In Excel you'd need a Worksheet_Calculate or a Workbook_SheetCalculate event handler. I don't believe Google Sheets has anything comparable. You may need to use App Script to change the contents of D2 whenever you change C2. After changing C2, if the value of C2 isn't checked, D2 would have the formula =today(); otherwise, if the value of C2 is checked, D2 would have the constant value of the date on which C2 was last changed to checked.

Something along the lines of

function onEdit(e) {
  var rng = e.range;
  var ws = rng.getSheet();
  var x;
  if (ws.getName() == "Sheet1" && rng.getA1Notation() == "C2") {
    if (rng.getValue() == "checked") {
      x = ws.getRange("D2").getValue();
      ws.getRange("D2").clearContent();
      ws.getRange("D2").setValue(x);
    } else {
      ws.getRange("D2").setFormula("=TODAY()");
    }
  }
}

When Sheet1!C2 changes to checked, Sheet1D2 becomes its value as a constant. When Sheet1!C2 changes to anything else, Sheet1!D2's contents becomes the formula =TODAY(). I believe that's the only way to do this in Google Sheets net of embellishment to refer to named ranges for Sheet1!C2, Sheet1!D2 and the value to compare against the value Sheet1!C2 instead of using the constant "checked".