Put Date & Time in a Cell

September 29, 2021 - by Bill Jelen

Problem: I need to enter the current date, current time, or current date and time in a cell. I don’t want to use NOW() or TODAY(), because those values will change over time. I want to lock in the current date or time.

Strategy: Ctrl+: enters the current time. That should be easy to remember, since 10:22 includes a colon. To enter the current date, use the same keys, but don’t press Shift. So, Ctrl+; puts in the current date.

What if you need both Date and Time? I learned this trick from Bob in Oklahoma City. Press Ctrl+: and then press Ctrl+;. Excel will show you the seemingly useless 10:23AM5/5/2019. When you press Enter, Excel will convert it to a real date and time as shown in row 4 below.

Ctrl + Colon gives you the current time. Ctrl + Semi-colon gives you the current date. Ctrl+Colon followed by semi-colon types something that looks like it won't work: 10:23AM4/4/2019 but after you press Enter, it puts in 4/4/2019 10:22. — Figure 95. Use Ctrl+: or Ctrl+; to apply a date or time stamp.

Gotcha: The Ctrl+: enters hours and minutes, but not seconds. If you enter Ctrl+: each second, 60 entries will be the same.

The Ctrl+Colon shortcut only gives you hours and minutes, not seconds. By using the macro listed below in the text, you can get the current time down to Hours, Minutes, and Seconds. — Figure 96. Ctrl+: does not provide seconds.

Alternate Strategy: Use a short macro stored in your Personal Macro Workbook and assign that macro to Ctrl+Shift+T. The macro can provide a time stamp that includes seconds.

First, make sure you have a Personal Macro Workbook. See "Create a Personal Macro Workbook" on page 69.

Switch over to VBA using Alt+F11. Display the Project Explorer using Ctrl+R. Open the Modules for Personal and type this short macro:

Sub TimeStampSeconds()
	ActiveCell.Value = Time
End Sub

Use Alt+Q to return to Excel. Press Alt+F8 to display the list of macros. Click once on TimeStampSeconds and click Options. Click in to the Shortcut key field and type Shift+T. You’ve now assigned the macro to Ctrl+Shift+T.

Close Excel. You will get a message asking if you want to save the changes to Personal. Be sure to answer Yes.

Alternate Strategy: If you want to create a Date & Time stamp that is accurate to the second, use this macro instead:

Sub DateTimeStampSeconds()
	ActiveCell.Value = Date + Time
End Sub

This article is an excerpt from Power Excel With MrExcel

Title photo by Jon Tyson on Unsplash