How to Insert and Use Timestamp in Excel?

n the picture below, we have demonstrated an example of inserting a timestamp in Excel.

Download the Practice Workbook

How to Insert a Timestamp in Excel?

Method 1 – Using Keyboard Shortcuts to Insert Timestamps

• If you want to insert only the time, use the following keys:

• For a date, use the following:

• For a complete date and time, use the following shortcut:

Method 2 – Using NOW and TODAY Functions to Insert Timestamps

Here is the overview of using the NOW function.

• To get the date and time together, use the following formula:

• To get the current date, put the following formula:

Method 3 – Inserting Timestamps Automatically by Applying Circular References

Here’s a brief review of the IF function which we’ll use below.

• Press Alt + F + T so the Excel Options window will pop up.
• Go to Formulas and select Enable Iterative Calculation.

• Use the following formula in C5:

The formula checks if the B5 cell is not empty. If the B5 cell is not empty, it checks if the C5 cell is not empty. If the C5 cell is not empty, it returns the value of C5. When the C5 cell is empty, it returns the current date and time. And when the B5 cell is empty, it returns an empty string.

• Press Ctrl + 1, and Format Cells will pop up.
• Select Custom from Category. For Type, choose dd–mm–yyyy hh:mm.
• Press OK.

• When you enter data into the Data column, it will add a timestamp of the last entry.

• Add “E” to the Data column, and the corresponding timestamp will pop up automatically.

Method 4 – Inserting Current Time with VBA

• Select Developer and go to Visual Basic.

• Go to the Insert tab and select Module.

• Insert the code, then run the code by pressing the Run button or F5.

Sub TimeStamp()
Dim s As Date
With Selection
.Value = Now
.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
End With
End Sub

• You get the timestamp after running the VBA code.

Method 5 – Creating a User-Defined Function to Insert a Timestamp

• We’ll use the Time_Stamp user-defined function to get the current timestamp.

• Create a module similar to the previous method.
• Insert the code below in the module and click on the Run button.

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

• As a result, you will get the timestamp.

Read More: How to Insert Current Date and Time in Excel

How to Use a Timestamp in Excel?

Case 1 – Inserting the Current Time as a Dynamic Value

We’ll use the INT function to get a semi-random value from time.

• Use the NOW function and change the formatting to Number. Here you can copy the formula below.

• You get the dynamic value according to the timestamp.

Case 2 – Converting a Timestamp to a Date

Change the format of C5 cell to Date after using the function.

Case 3 – Removing the Time from a Timestamp

Use INT and DATE functions to remove time from timestamp. We will give a brief overview of the DATE function.

• Format the C5 cell into Date format.
• Use the following formula in C5.

• Use the following formula in the D5 cell after formatting the cell as Date.

Case 4 – Getting Hours, Minutes, and Seconds from a Timestamp in Excel

Here is a brief overview of time functions in Excel.

• Here are the functions that will separate the hour, minute, or seconds part of the timestamp from cell B5.

Case 5 – Inserting a Specific Time in Excel

We will use the TIME function in Excel to insert a specific time in Excel.

• Use tjhe following formula to turn the values into time:

• The formula will return time accordingly.

Read More: How to Insert Last Modified Date and Time in Excel Cell

Case 6 – Inserting a Timestamp in Excel When Cell Changes

• Right-click on the worksheet and select View code.

• Insert the code given below and press Run.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellCol As Integer, TimeCol As Integer, Row As Integer, Col As Integer
    Dim DpRng As Range, Rng As Range
    Dim Timestamp As String
    CellCol = 2
    TimeCol = 3
    Row = Target.Row
    Col = Target.Column
    If Row <= 4 Then Exit Sub
    Timestamp = Format(Now, "DD-MM-YYYY HH:MM:SS AM/PM")
    If Target.Text <> "" Then
        If Col = CellCol Then
            Cells(Row, TimeCol) = Timestamp
        Else
            On Error Resume Next
            Set DpRng = Target.Dependents
            For Each Rng In DpRng
                If Rng.Column = CellCol Then
                    Cells(Rng.Row, TimeCol) = Timestamp
                End If
            Next Rng
            On Error GoTo 0
        End If
    End If

• If you change any Data Entry column, it will show the time in the Timestamp column.

Things to Remember

• Automatic Updates: Timestamps based on formulas like =NOW() or =TODAY() update automatically whenever the worksheet recalculates or when triggered by specific events. Keep in mind that if you want to freeze a timestamp, you need to convert it to a static value.
• Formatting Options: Excel provides various formatting options for timestamps. You can customize the date and time format using the built-in number formatting options or the TEXT function. Choose a format that best suits your needs and makes the timestamp easily readable.
• VBA Automation: If you require more advanced timestamp functionality or want to automate timestamp insertion based on specific events or conditions, you can use the VBA code. VBA allows customization and flexibility in determining when and where to insert timestamps.
• Time Zone Considerations: Excel does not have built-in functions to directly convert timestamps to different time zones. If you need to work with timestamps in different time zones, you may need to use VBA code or external tools to handle the conversion accurately.
• Consistency and Accuracy: Ensure that you insert timestamps consistently and accurately. Double-check formulas, VBA code, or macros to verify that you correctly calculated and inserted the timestamps in the desired cells or ranges.

Frequently Asked Questions

Can we freeze a timestamp so that it doesn’t change automatically?

Yes, if you want to freeze a timestamp and prevent it from updating, you can convert the formula or value to a static value. Select the cell with the timestamp, copy it (Ctrl + C), then use “Paste Special” and select “Values” to paste it as a static value.

How can we insert a timestamp when a specific cell or range is modified?

You can achieve this using VBA code and the Worksheet_Change event. Write VBA code to check if the modified cell is the specific cell or within the specified range, and if so, insert the timestamp using the NOW() function or by assigning the current date and time to the cell.

Can we create a button or shortcut to insert a timestamp with one click?

Yes, you can create a button on the Excel toolbar or ribbon using the Developer tab. Assign a VBA macro to the button that inserts the timestamp using the desired method (e.g., NOW() function or VBA code).

Is it possible to display the timestamp in a specific time zone?

Excel does not have built-in functions to directly convert time zones. However, you can use VBA code or external tools to convert the timestamp to a different time zone based on your requirements.

Can we customize the timestamp update frequency in Excel?

Timestamps based on formulas like =NOW() update whenever the worksheet recalculates, which can be controlled through Excel settings. You can manually recalculate the worksheet or change the calculation mode to control the update frequency. VBA code can provide more control over the update frequency by triggering the timestamp insertion based on specific events or conditions.

Timestamp in Excel: Knowledge Hub

<< Go Back to Date-Time in Excel | Learn Excel