How to Insert a Timestamp in Excel ?

In this post, you’ll learn how to create a timestamp in Excel using 5 different ways and we will try to figure out this is the best out of all. So let’s get started.

First Method: Using a Keyboard Shortcut to Insert a Timestamp

There are two different shortcuts to insert a date and a time. And, here we need to use both of them subsequently. Here are the steps:

1. First of all, select the cell where you need to insert a timestamp.
2. After that, use the shortcut key Control + : (Press and hold control and then press colon). Once you press this, it will insert the current date (according to your system) in the cell.
3. At this time, your cell is in edit mode.
4. Now, press Control + Shift + : (Press and hold control and shift key and then press colon).
5. Your cell is still in edit mode, now press the enter key to complete the entry.

In short, you need to press two shortcuts in sequence to insert this. And, if you want to add only one thing out date and time, just skip the shortcut key that.

Second Method: Insert a Timestamp with NOW Function

A simple dynamic method. If you want to use a formula to insert a timestamp, the perfect way is to use the NOW function. When you enter this function in a cell it returns the current date and time according to your system’s settings.

The default format of date and time return by NOW is mm/dd/yyyy hh:mm. But for some reason, if you want a custom format, you change its format using the custom format option. Select the cell ➜ Press shortcut key control + 1 ➜ Select “Custom” ➜ Enter “mm/dd/yyyy hh:mm” in input box ➜ Click OK.

And if you want to enter the only date then you can use TODAY instead of NOW, it only returns the current date according to the system’s settings.

Pros of this method

1. It’s a dynamic method.
2. You can use both of the functions with an IF function to create a condition to enter a timestamp if another cell has a value.

Cons of this method

1. Even it’s a dynamic method but as both of the functions are volatile they will get updated whenever you make changes in your worksheet.
2. And if you just want values instead of formulas you need to convert them into values manually.

Third Method: Using Circular Reference for Creating a Timestamp

If you want to get into an advanced method and don’t want to use methods #1 and #2 then you can use circular reference to insert a timestamp.

But before you learn this method let’s understand what circular reference is all about. Let’s say you have value 5 in cell A1 and value 10 in cell B1. Now if you enter a formula =A1+B1+C1 in cell C1, it will return a message circular reference error.

This is because you are using cell C1 as a reference in cell C1. When a circular reference error happens, there is a non-ending loop in the cell. Like: reference in cell A3 is dependent on the value of cell A3 and the value of A3 is dependent on reference to cell A3.

But when a circular reference is entered, Excel doesn’t calculate it and the non-ending loop never starts.

Here’s the deal:

You can enable the “iterative calculation option” to force Excel to perform the calculation at least one time and use the now function in the calculation. This way Excel will update the cell formula only one time instead of every time. Steps to enable iterative calculation option:

1. Go to File ➜ Options.
2. In the Excel options, select Formulas.
3. In the Calculated options, check the Enable iterative calculation option.
4. Click OK.

After that in the cell B2, enter below formula in the formula bar: =IF(A2<>””,IF(B2<>””,B2,NOW()),””)

Now when you enter any value in cell A2 the formula in cell B2 will return a timestamp.

Forth Method: VBA to Add a Timestamp

If you are a VBA freak then I’m sure you’ll find this VBA code useful. With this, you don’t need to enter a formula or even not use any shortcut key. Just select the cell where you need to enter a timestamp and run the macro.

Sub timeStamp()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = “m/d/yyyy h:mm:ss AM/PM”
End With
End Sub

How to use this code

To use this code you can add it on QAT (quick access toolbar) and run it every time whenever you need to add a timestamp.

Here are the steps:

• Add this code in module by opening VBA editor.

• After that, go to File -> Options -> Quick Access Toolbar.
• Choose “Macros” from “Choose Command from” drop down.
• After that, add timestamp macro to QA

• Now select that macro click modify button.

• Select an icon and click OK.

Now you have an icon on QAT and whenever you need a timestamp you can select the cell and click this button to insert it.

Also, Using UDF for Timestamp

Yes, you can also create a custom Excel function for inserting a timestamp in Excel. Below is the code for this UDF.

Function Timestamp(Reference As Range)
If Reference.Value <> "" Then
Timestamp = Format(Now, "dd-mm-yyyy hh:mm:ss")
Else
Timestamp = ""
End If
End Function 

By using this user-defined function you can get a timestamp in a cell if another has a value in it. Please follow the below step

• Go to Developer tab and open VBA editor.
• In VBA editor, insert a new module and paste this code into it.
• Now, close VBA editor and come back to your worksheet.
• In the cell B2, enter below formula.

• Now, when you enter any value in cell A1, cell B1 will get a timestamp.

Conclusion

Adding a timestamp is something we often do while working in Excel. And, you have 5 different methods to insert it. We hope that the tutorial was beneficial to many of our readers.