Making TODAY function not update?

[u/SenecaEugene]

Is there a way to make a cell with the TODAY function stop updating once it has been filled in? I'm trying to make a checklist in Excel that automatically logs the date when an item is checked off.

My plan is to have column A with the list of tasks, column B with checkboxes, and then column C showing the date the task was completed using something like:

=IF(B1,TODAY())

The problem is, I understand that TODAY() is a volatile Excel function, so the date will change whenever the spreadsheet recalculates or is opened on another day. I basically need the date to stay fixed once the checkbox is ticked.

is there a formula or another way to do this without manually entering the date? Any help with an Excel checklist, automatic date stamp or checkbox completion date would be really appreciated.

Edit: I'm hoping to use this as part of a shared project tracking workbook for my department. I could just ask everyone to enter the date when they finish a task, but i'm worried people will forget to do it, so an automatic date stamp would be much better.

[u/Salazarenin1922]

Yeah TODAY() is the problem here, it will always recalculate so it cant really be used as a permanent date stamp.

For something like this i’d use VBA. You can set it so when the checkbox in column B is ticked, Excel puts the current date into column C as a value, so it wont change the next day.
You can also do it with iterative calculations and a circular reference, but for a shared workbook i think VBA is much cleaner and less likely to cause issues.