Tom’s Tutorials For Excel: Entering a Static Date and Time

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , , ,
37 comments on “Tom’s Tutorials For Excel: Entering a Static Date and Time
  1. Bill says:

    Is it possible to include the keystrokes into a formula or somehow program Excel to simulate the keystrokes automatically?

    • Tom Urtis says:

      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

  2. Aasif T says:

    How to enter a static date in excel on my mobile (android), can u suggest something

    • Tom Urtis says:

      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.

  3. Davohat says:

    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

    • Tom Urtis says:

      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.

  4. David Littlechild says:

    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.

    • Tom Urtis says:

      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”.

  5. Davohat says:

    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.

  6. Woniok Chung says:

    Great tips. I’ve got helped by this article. Thank you a lot!

  7. Corey says:

    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!

    • Tom Urtis says:

      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.

      • aiko says:

        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?

        • Tom Urtis says:

          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.

  8. Matt says:

    In this instance =if(isblank(c5),””,now())
    How would I make the date that populated a static date to mark when c5 was changed?

    • Tom Urtis says:

      You mean static after C5 was first changed, meaning any second, third, fourth etc change to C5 would not change the date,
      static each time cell C5 gets changed.

      • Matt says:

        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.

        • Tom Urtis says:

          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.

          • Matt says:

            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.

            • Tom Urtis says:

              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
              If Len(Target.Value) > 0 Then
              .Locked = True
              .NumberFormat = "mmmm d, yyyy"
              .Value = VBA.Date
              End with
              End Sub

              Widen column B as needed to accommodate whichever Date format you use, if you don’t want the one I exampled here.

  9. Kyle says:

    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.

    • Tom Urtis says:

      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
      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?

      • Kyle says:

        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!

  10. JanetRodgers says:

    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!

  11. Chuck says:

    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!

  12. Chuck says:

    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.

    • Tom Urtis says:

      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).

      • Chuck says:

        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.

  13. Chuck says:

    OPPS!!! The second 0 did not show the NOT symbol. Should read “changes from 0 to NOT 0”.

    • Tom Urtis says:

      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.

      • Chuck says:

        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.

        • Tom Urtis says:

          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.

  14. Chuck says:

    OPPS!!! The second 0 did not show the (combined Less Than, Greater Than text symbols). Should read “changes from 0 to NOT 0”.

  15. Chuck says:

    My Apologies for failing to supply sufficient information, especially when requesting assistance.

    And my sincere “THANK YOU !!” for your help.

    Best Regards

  16. Neil Potter says:

    Fixed – see
    He gives 2 solutions, one that does not need VBA!

    • Tom Urtis says:

      Well, the tradeoff, which I would not recommend enduring, is to change the circular reference acceptability tolerance that favors that one particular situation, but which also will apply to any circular reference anywhere else not only in that workbook but for the entire Excel application. This reminds me of the Precision as Displayed option that affects all calculation result appearances just to satisfy one concern to make it visually “look right”.

      The other reason I do not recommend the formula option is that you need to pre-enter the formula for as many rows as it might be needed, so the user does not need to enter it or copy it down when they make a new entry on the next rows. I am aware of Tables that partially compensate for that but I am not a fan of Tables and never use them.

      But, I appreciate your comment, thank you. If the video-demonstrated option (the link in your comment) can suit other users’ needs, so much the better for them.

      I only wish that the makers of these videos would spell out all the downsides to their solutions so the people who view their videos can make an informed decision by realizing all the tradeoffs and minefields.

Leave a Reply

Your email address will not be published. Required fields are marked *