How to Insert Timestamp in Excel (6 Easy Ways)

In this article, you will learn how to insert timestamp in Excel. Here, I will show you different methods for inserting timestamps. So, you can use it in your convenient way.

You will see the use of Excel functions for including a timestamp. Then, you will get the idea of applying keyboard shortcuts to place timestamps. Not only this but also you will get some VBA codes for putting timestamps.

Timestamps show the exact time when something happens. Timestamps provide a way to know the exact sequence in which different events or actions occurred. You can keep a record of the time through timestamps when you are changing or modifying or updating the data. They also help us to schedule tasks at the right times.

Actually, timestamps are like a timeline that allows us to coordinate actions across different devices or systems, making it easier to manage processes and analyze data.

Download Practice Workbook

You are recommended to download the Excel file and practice along with it.

Insert Timestamp in Excel: 6 Easy Ways

Here, I will discuss 6 easy ways to insert a timestamp in Excel.

Some of these methods will return dynamic timestamp and some of these will give static timestamp. With the static or fixed timestamp, you can store data. This will help you to preserve data with accurate date and time.

However, the dynamic one will automatically refresh the inserted timestamp after every opening of the workbook. So, while you need to immediately work with a file then you can use the dynamic methods of timestamp.

Now, let’s see the methods below and learn how to insert a timestamp in Excel.

1. Using NOW Function to Insert Timestamp

The NOW function returns date and time with format MM/DD/YYYY hh: mm. Which denotes timestamp. Here, you don’t need to use any references, just write the function and press ENTER to get the timestamp.

• Go to the cell (E5) where you want to insert a timestamp.
• Use the following function.

• Press ENTER.

NOW is a volatile function which means it’ll update every time you input or change any data in your Excel spreadsheet. To keep the time static found by the NOW function, you have to copy the time from the cell and then paste it with the Values (V) option.

• So, press CTRL+C to copy the cell.
• Right-click on the cell where you want to put the value (or in this case, the same cell).
• From the Context Menu Bar >> go to Paste Options >> select Values (V).

As you can see from the following image, E5 cell doesn’t update with entering another timestamp in E11 cell.

2. Applying Keyboard Shortcut for Including Static Timestamp

You can use a keyboard shortcut for inserting a static timestamp. Suppose you want to preserve some data with the date and exact time of every entry in that dataset. In this case, you can use this method.

• Go to the E5 cell >> press CTRL+;(Semi-colon) >> press Space >> CTRL+SHIFT+;(Semi-colon) >> get the output.

Here, you will get the time in 24 hours format. But you don’t get the seconds through this way.

3. Applying VBA to Insert Timestamp with Second in Next Cell

Say you insert any value/ update a value and you want to store that exact time. So, here is the solution for you. See the following steps carefully.

• Right-click on sheet name >> from Context Menu Bar >> select View Code.

• Now write the following code in the sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Value <> "" Then
Application.EnableEvents = False
Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")
Application.EnableEvents = True
End If
End Sub

• Now, save the code >> go back to the worksheet >> enter/modify a value in the D column >> get the instant time of update in the E column.

4. Using a Defined Function to Place Timestamp with Seconds

If you want to insert a timestamp based on another cell value, then this method is for you. Also, through this method, you will get the timestamp along with seconds visible to the cell. Let’s see the steps below.

• From the Developer tab >> go to Visual Basic >> so you will get the VB Editor window.
• Now, in VB Editor, from Insert tab >> click on Module.
• Then, in the Module >> write the following code >> press on the Save button.

Function insert_Time_Stamp(my_cell As Range)
If my_cell.Value <> "" Then
    insert_Time_Stamp = Format(Now, "dd-mm-yyy hh:mm:ss")
Else
    insert_Time_Stamp = "No value in D column"
End If
End Function

• See the following image. So, use equal sign (=) >> write insert >> you will get some suggestions from Excel >> choose insert_Time_Stamp >> give cell reference >> see the result.

5. Applying VBA with Button to Put Timestamp in Excel

There is another easy way to insert timestamps in defined cells. You can use a very simple VBA code for this. Through this method, you can store the timestamps in any cell with just one click.

• To do so, from the Developer tab >> go to Visual Basic >> so you will get the VB Editor window.
• Then, in VB Editor, from Insert tab >> click on Module.
• After that, in the Module >> write the following code >> press on the Save button.

Sub inserting_Timestamp()
With Selection
    .Value = Now
    .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
End Sub

• Go back to the worksheet >> from the Developer tab >> go to Insert >> select Button (Form Control).
• Drag the Button to a suitable place.

• After that, you will get the Assign Macro dialog box.
• Select the sub-procedure (inserting_Timestamp) >> press OK.

• As a result, go to the target cell >> click on the Button >> get the timestamp.

6. Combining IF & NOW Functions with Circular Reference for Timestamp

When you need to introduce timestamps for many cells, then a method by which you can keep the timestamps of the filled cells at once, is more convenient, right?

To do so, I will use circular references. Circular reference means the formula includes the cell too, where you are writing the formula. But at first, you must give the permission to Excel for considering the circular reference. Otherwise, it will show you an error. So, here is the procedure.

• From Top Ribbon >> go to File tab >> click Options.
• So, you will get the Excel Options dialog box.
• Now, in that dialog box >> go to Formulas >> check Enable iterative calculation >> press OK.

• Now, enter the following formula in the E5 cell.

• Press ENTER >> drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.
• See the following image, the timestamps are inserted only for the valued cells of the D column.

How to Convert Excel Timestamp to Date

Timestamp means the current date and time both. You can extract only the date value from this timestamp too.

• To do so, select the timestamp >> press CTRL+1 to open the Format Cells dialog box.
• In the Format Cells dialog box >> from Number >> go to Date >> select the format >> press OK.

• Finally, this will convert the time to date.

Frequently Asked Questions

1. How to Get Current Time from Timestamp?

You should only press CTRL+;(Semi-Colon) to get the current time instead of the timestamp.

2. What is the format of a timestamp?

YYYY-MM-DD hh:mm:ss → Year-Month_Day + Hour-Minute-Second.

3. Can I filter data by timestamp?

Select any cell of dataset >> press CTRL+SHIFT+L >> you will get the drop-down arrow beside column headers >> click on the drop-down arrow beside the column having timestamp >> then you will get the option to filter data based on year/month/time.

Conclusion

This is for today. Here, I have tried to explain all the suitable ways to insert a timestamp in Excel. At first, you should read through all the discussed ways and along with this practice them. Then you may select your convenient method for solving problems or usage.

If you have any queries regarding this, then comment below. Also, you can contact us through email.