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: , , , , , , , , , , , , , ,
21 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.

Leave a Reply to Tom Urtis Cancel reply

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