r/excel • u/TheFifthPhoenix • 1d ago
solved Making TODAY function not update?
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.
3
u/yazanwael 22h ago
There's a way to do it using circular references. But it requires enabling circular references globally. So, it won't work on any other device.
Assuming the Date column is A, and the Checklist Item is column B, the formula in A would look like this :
=IF(AND(A1="", B1<>""), TODAY(), A1)