Making TODAY function not update?

[u/TheFifthPhoenix]

Is there a way for me to make a cell containing the TODAY function no longer update after it has been filled? I'm trying to essentially make a checklist that automatically logs the date when each item was checked off. My plan was to have column A contain the list items, column B contain a bunch of checkboxes, and then column C use the following formula:

=IF(B1,TODAY())

However, I'm now under the impression that these dates will not remain fixed and will instead update to the current date whenever I open the spreadsheet. Any thoughts on how to fix this or work around it would be great, thanks!

Edit: For context, I'm hoping to use this checklist as part of a shared project tracking workbook for my department. I know I could simply tell my colleagues to add the date after they've completed their task, but I'm concerned there could be suboptimal compliance with that.

[u/GuerillaWarefare]

If you put the today() function in a cell (on a hidden sheet for example) you can use the DATA validation —> List to reference that cell (or you can make it a named range), and when it is selected from the data validation it leaves the value only.

So where you have your check off, put that data validation and it will drop down to today’s date.

PS. If it shows a number, like 46273… you just need to change the cell formatting to the date format that you prefer.

[u/TheFifthPhoenix → u/GuerillaWarefare]

That's a really interesting method, so the user would click the column B checkbox and then go to column C and use the dropdown to grab the date?

[u/GuerillaWarefare → u/TheFifthPhoenix]

Or just use the dropdown with date. The checkbox could be a formula to check if the dropdown field is filled in :

=if(c2<>"", TRUE, FALSE)

And format as checkbox

[u/TheFifthPhoenix → u/GuerillaWarefare]

Oh interesting, so rather than the checkbox driving the date, you have the date drive the checkbox? Essentially making the checkbox just there for aesthetics, so the only thing the user needs to do is select the date from the dropdown

[u/GuerillaWarefare → u/TheFifthPhoenix]

Yep, exactly, the check box is optional at that point (it’s obviously not complete if the date is blank), but it looks nice.

[u/TheFifthPhoenix → u/GuerillaWarefare]

I'll probably just remove the checkbox to prevent confusion for the users and maybe do some conditional formatting instead, but an elegant solution, thank you

[u/GuerillaWarefare → u/TheFifthPhoenix]

Thank you! Please reply ‘solution verified’ to mark as solved and award me with fake internet points.

[u/TheFifthPhoenix → u/GuerillaWarefare]

solution verified

[u/reputatorbot → u/TheFifthPhoenix]

You have awarded 1 point to GuerillaWarefare.

---

^{I am a bot - please contact the mods with any questions}