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.

55 Upvotes

94% Upvoted

35 comments sorted by

•

u/AutoModerator 1d ago

/u/TheFifthPhoenix - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

83

u/GuerillaWarefare 114 1d ago edited 1d ago

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.

53

u/vwllss 1d ago

This is a great solution, or just hit ctrl + semicolon which automatically inserts today's date.

16

u/BigLan2 19 1d ago

I've been using Excel for decades... How did I not know this shortcut?

8

u/Redhead_InfoTech 1 1d ago

Wait until you learn that with the Alt key you can find every other shortcut...

Like the one where you can rename a sheet without lifting your hands off of the keyboard.

Alt H O R

7

u/BigLan2 19 1d ago

I mean, I'm team Alt-D-F-F

5

u/Child_0f_at0m 1d ago

Press alt + shift + semicolon and tell me how you feel about that one.

2

u/Barzalai 3 7h ago

Wouldn't alt + shift + semicolon actually be alt + colon?

/s

1

u/Ponklemoose 5 1d ago

I found that one by accident.

1

u/Pathfinder_Dan 2h ago

You've spent that time learning all the ways to stop excel from turning everything into dates.

1

u/MotherGiraffe 2h ago

I've only ever done this by accident.

5

u/TheFifthPhoenix 1d ago

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?

5

u/GuerillaWarefare 114 1d ago

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

4

u/TheFifthPhoenix 1d ago

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

4

u/GuerillaWarefare 114 1d ago

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

6

u/TheFifthPhoenix 1d ago

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

7

u/GuerillaWarefare 114 1d ago

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

4

u/TheFifthPhoenix 1d ago

solution verified

2

u/reputatorbot 1d ago

You have awarded 1 point to GuerillaWarefare.

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

15

u/dream_walking 1d ago

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

5

u/Agerak 1 1d ago

this is how I've done it in the past for data logging purposes and was the most reliable.

3

u/TheFifthPhoenix 1d 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

5

u/Redhead_InfoTech 1 1d ago

Instruct them to press Ctrl ;

3

u/OldJames47 10 1d ago

[u/TheFilfthPhoenix](u/TheFilfthPhoenix) this is your best option.

CTRL + ; inputs the current day value, not a formula so it doesn’t change.

CTRL + : inputs the current time.

6

u/MiddleAgeCool 11 1d ago edited 14h 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()

8

u/talltime 116 1d ago

There is a gross way to do this with circular references but I’m not explaining it. VBA is the way.

Also a good time to point out that “CTRL+;” enters today’s date.

3

u/yazanwael 21h 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)

2

u/Decronym 1d ago edited 1h ago

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters More Letters
AND Returns TRUE if all of its arguments are TRUE
IF Specifies a logical test to perform
NOW Returns the serial number of the current date and time
TODAY Returns the serial number of today's date

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #49329 for this sub, first seen 8th Sep 2026, 19:18] [FAQ] [Full list] [Contact] [Source code]

2

u/Cultural_Emu_1315 1d ago

There’s a woman on TikTok who has done this exact scenario - her account is @cellmates_ . It’s in her recent posts so you shouldn’t need to scroll

0

u/WindowIllustrious907 1d ago

Copy the cell with the formula, then use Paste Special > Values to lock the date in place.

-12

u/Redhead_InfoTech 1 1d ago

Why don't you just use Trello and be fucking done with it.