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

Leave a Reply

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

*