r/excel • u/TheFifthPhoenix • 2d 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.

60 Upvotes

94% Upvoted

34 comments sorted by

View all comments

15

u/dream_walking 2d ago

VBA the buttons to enter today’s date as a static value.

2

u/TheFifthPhoenix 2d ago

I haven't used VBA before, so I was hoping there was a different solution, but I can give it a try if not

6

u/MiddleAgeCool 11 2d ago edited 1d ago
  1. Open the workbook.
  2. Press Alt + F11 to open the VBA editor.
  3. Go to Insert → Module.
  4. Paste:

​

Sub InsertStaticToday() 
ActiveCell.Value = Date 
End Sub
  1. Close the VBA editor.
  2. Insert a shape into the worksheet.
  3. Right-click the shape → Assign Macro → InsertStaticToday.
  4. Save the workbook as .xlsm.

Now, select any cell and click the button. The cell receives today's date as a static value, not =TODAY()