Tom’s Tutorials For Excel: Entering a Static Date and Time
As you probably know, you can enter =TODAY() and get the current date, or =NOW() and format the cell as a time to get the current time.
Those functions belong to a category of volatile functions, meaning they update themselves whenever the workbook changes, or is opened and closed. Sometimes, you want to enter a date or a time and have that entry be static — not changing unless you intentionally change it.
To enter the current date as a static (unchanging) value in a cell, press the Ctrl+; keys.
To enter the current time as a static (unchanging) value in a cell, press the Ctrl+Shift+; keys.
To enter a static date and time:
Step 1
Enter =NOW() in a cell as shown.
After that, to easily change that formula value into a static value:
• Select the cell.
• Press and release the F2 key.
• Press and release the F9 key.
• Hit the Enter key.
Step 2
It’s usually a good idea to format the cell to display a recognizable date and time. Right-click the cell, left-click to select Format Cells, and on the Number tab select your desired date and time format, then click OK.
Is it possible to include the keystrokes into a formula or somehow program Excel to simulate the keystrokes automatically?
Yes, it is possible but it requires VBA, with a user defined function or, in your case, assigning this macro to a custom key stroke like Ctrl+Shift+T
Sub myNow()
activecell.Value = now()
End Sub
How to enter a static date in excel on my mobile (android), can u suggest something
Excel worksheets and smartphones are not a good combination for data manipulation when it comes to the kind of functionality you are asking about. I understand what you are asking and I totally get why, but I just don’t work with Excel that way on such a small interface and so I cannot unfortunately offer an answer you can use. Sorry that I could not be of better help, but perhaps someone reading this has experience in this area and can chime in with a suggestion.
If I enter a comment in one cell I want another cell to automatically populate with today’s date but I want the date to then remain static. How do I do this please
This is achievable but the way to do it depends on how you want that date to actually remain static. For it to be really really static, meaning it cannot be deleted manually, do you gave the worksheet protected and those date cells Locked so no one can manually delete the dates. Or do you not care so much about that and you want to allow for manual deletion.
Hi, i am happy for manual deletion in the event that the data input in the comment cell is incorrect. I have not currently protected the work book but is something i may consider once finished.
I forgot to ask for clarification on this you wrote:
“enter a comment”.
Does that really mean that you place a comment in a cell such as right-clicking a cell and selecting Insert Comment? Or maybe did you just mean that you would type some text into the cell itself and have that be your “comment”.
The comment is an option from a drop down list in say cell A1 and the date appears in A2 the date box is the date you select an option from the list in cell A1.
Great tips. I’ve got helped by this article. Thank you a lot!
Thank you!!
Hi,
Not sure if I missed this but it looks like the comments are asking how to have the date/timestamp automatically show up when you enter data into another column, and then have the date/timestamp remain static (as in, unchanging). Every article I’ve looked at so far uses the formula =IF(A2””,IF(B2””,B2,NOW()),””) but this has the timestamp automatically changing to the current time which makes it useless as a timestamp. Thank you!
The comments want a static data and time; anything else does not make sense because with a NOW or TODAY volatile function, those dates and times would be constantly changing. What I posted is what they wanted.
If so, is there a way for this formula’s output =IF(A2””,IF(B2””,B2,NOW()),””) to be static? Aside from using the =now function, what can we do for this to be unchanging?
If VBA is acceptable to you, it is an option. I do not understand why you wrote “Aside from using the =now function…” when the NOW function seems to be the centerpiece of what you are doing.
In this instance =if(isblank(c5),””,now())
How would I make the date that populated a static date to mark when c5 was changed?
You mean static after C5 was first changed, meaning any second, third, fourth etc change to C5 would not change the date,
or
static each time cell C5 gets changed.
C5 is a drop down list of names. Once name is assigned in c5 it will not change. So what ever of the solutions is the easiest.
I posted this previously on my social media channels that sounds like what you want. Change the cell reference from C2 as shown in the picture to C5, and put the code into the worksheet module, making sure you save the workbook as a .xlsm if you have not already done so.
https://www.atlaspm.com/wp-content/uploads/2022/04/Slide384x.png
That is similar yes. What I am looking for is the cell that the formula is in(B5) will generate the date when a selection is made in C5. Once the document is closed and re opened it will retain the date that a selection was made in C5.
Well, the dependent cell of interest was a missing piece of the puzzle you did not specify until now.
So, format cell B5 as Unlocked.
This code that should work in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address <> "$C$5" Then Exit Sub
With Range("B5")
If .Locked = True Then Exit Sub
ActiveSheet.Unprotect
If Len(Target.Value) > 0 Then
.Locked = True
.NumberFormat = "mmmm d, yyyy"
.Value = VBA.Date
End with
ActiveSheet.Protect
End Sub
Widen column B as needed to accommodate whichever Date format you use, if you don’t want the one I exampled here.
Tom,
This has been very helpful utilizing the F2 and F9 keys to make the time and date static.
Could you possibly give and example of how to code using VBA to say make this function work utilizing the a code like =Now(S) or CTRL + SHIFT + S?
Tired looking up a few videos on it but couldn’t make it work.
So what you are asking to do is,
instead of 3 key presses (F2 and F9 and Enter),
you want either
4 key presses (Ctrl and Shift and S and Enter
or
8 key presses (= and N and o and w and (and S and ) and Enter
???
It id do-able but why make more work for yourself?
Was just trying to get one step instead of having to type in =Now() then having to re-highlight the cell to click F2, F9 and then enter.
But maybe easier to just leave it this way for future use for other people instead of creating codes just to create a code. I see where your heading!
For Pete’s sake, why does it take a computer science degree to do what the original request was? I have a list of things, and when I enter text in one cell (like ‘fed the dog’) I want Excel to put the date and time I did that in another cell!
I have a similar request. Can you use another cells contents as a trigger to enter the static date only? example: Cell A2 contains “Last Name”. Cell B2 contains “IF(A2=”Last Name”,””,NOW()). Once a name is inserted in A2 it may change, (typo on my part) but never to “Last Name”.
All assistance on my behalf is Greatly Appreciated!
I think I found an answer to my question on one of your Tweets about changing a date/time if another cell was changed. You used a Worksheet_Change event procedure to accomplish that requirement. I think I can use that for my request.
Thank you for sharing your knowledge. Excel can be frustrating/intimidating.
Thanks for the follow-up, and yes that would be the way to go, assuming the entry to that other cell is done manually or by copy and paste (as opposed to a formula).
Another question please. After re-reading your above comment “(as opposed to a formula)”, can that be done?
I have a cell that calculates a numeric value (M43). I would like to use it to trigger insertion of a Static Date in another cell (M10). I foresee the first date insertion occurring when the calculated cell (M43) changes from 0 to 0. Then, IF the value changes again (may increase or decrease), the static date cell (M10) would be updated.
OPPS!!! The second 0 did not show the NOT symbol. Should read “changes from 0 to NOT 0”.
This would be a Worksheet Calculate event because a formula is in the triggering cell.
What is not clear is this you wrote:
“Then, IF the value changes again (may increase or decrease), the static date cell (M10) would be updated.”
Does that mean only once (the first and only time the cell would change from 0 to something else), or everytime when and after cell M43 changes.
Cell M43 calculates a total. The answer to your question is Yes. The Date should indicate the last change made to the total. That involves up to 20 new values to M43.
I considered using an AND function on the change of the last calculated value to delay the set the date. The last calculated value comes from cell C36.
ie. IF(AND(C36=”zzz”,M43=”zzz”), TRUE, FALSE).
Once again, THANK YOU for sharing your knowledge.
It is not clear what you want to be the case when cell M43 has a calculated number in it that is not zero, but a future calculation returns a zero. In that case this code makes cell M10 show 0 and not a date.
Right-click onto your worksheet tab and left click to select View Code. Paste the following code into the large white area that is the worksheet module. Press Alt+Q to return to your worksheet.
Also make sure your workbook is saved with the .xlsm (macro enabled) extension.
OPPS!!! The second 0 did not show the (combined Less Than, Greater Than text symbols). Should read “changes from 0 to NOT 0”.
My Apologies for failing to supply sufficient information, especially when requesting assistance.
And my sincere “THANK YOU !!” for your help.
Best Regards