How to Insert and Use Timestamp in Excel?

In this article, you will learn 5 suitable ways to insert a timestamp in Excel with 5 practical applications of timestamp in Excel. The ways are: Using keyboard shortcuts, Using NOW and TODAY functions, applying circular references, inserting current time with VBA, and creating user-defined functions.

While preparing this article, we used Microsoft 365 for applying all operations but they are also applicable in all Excel versions.

Inserting a timestamp in Excel allows you to track and manipulate data based on specific time intervals, such as minutes, hours, days, or even years. By inserting a timestamp, you can create a structured timeline in Excel, making it easier to organize and analyze your data. Whether you are working with sales figures, stock prices, or any other time-dependent data, adding a timestamp allows you to easily track changes, identify patterns, and make informed decisions.

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

Download Practice Workbook

Timestamp in Excel.xlsm

How to Insert Timestamp in Excel?

In this section, you will come across five different ways to insert timestamps in Excel, with the necessary explanation. You will use keyboard shortcuts, combine NOW and TODAY functions, use VBA code, and so on.

1. Using Keyboard Shortcuts to Insert Timestamp

Here, you have to use the keyboard shortcut to get the timestamps accordingly.

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

Ctrl + Shift + ;

In case of date, use the following things:

Ctrl + ;

Again, the keyboard shortcut will be as follows in case you want to date and time simultaneously:

Ctrl + Shift + ; [Space] Ctrl + ;

What Are the Advantages and Disadvantages of This Method?

Advantage: It is a quick and easy way to insert a timestamp without requiring any additional setup or code.
Disadvantage: It requires manual intervention each time you want to insert a timestamp, which can increase the likelihood of errors or omissions.

2. Using NOW and TODAY Functions to Insert Timestamp

Here we are using NOW and TODAY functions to insert the timestamp.

In this section, you have to use the NOW and TODAY functions to get a timestamp. You must use the following formulas in the B5 and B6 cells respectively.
To get the date and time together, the formula will be in the B5 cell:

=NOW()

To get the current date, the formula will be in the B6 cell:

=TODAY()

Why Should You Use This Method?

Advantage: It updates automatically whenever the worksheet is recalculated or when the functions are triggered, providing real-time timestamps.
Disadvantage: It requires manual intervention each time you want to insert a timestamp, making it prone to errors or omissions.

3. Inserting Timestamp Automatically by Applying Circular Reference

Here you have to use IF and NOW functions to apply circular reference. Using circular reference, you will be able to insert timestamps automatically. The formula explanation of the NOW function has already been given above. Now There is a brief review of the IF function below.

Click here to enlarge the image

At first, you are going to press Alt+F+T and then the Excel Options window will pop up.
Then, go to Formulas and select Enable Iterative Calculation.

Click here to enlarge image

Now, you will write the formula in the C5 The formula is:

=IF(B5<>"",IF(C5<>"",C5,NOW()),"")

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.

Next, press Ctrl + 1, and Format Cells will pop up.
Now, select Custom from Category and Type: dd–mm–yyyy hh:mm.
Then, press OK.

Finally, you get the dataset below. When you give data to the Data column, it will add a timestamp.

Finally, you add “E” to the Data column, and the corresponding timestamp will pop up automatically.

What Are the Advantages and Disadvantages of This Method?

Advantage: It automatically updates the timestamp whenever any cell on the worksheet is changed, providing real-time timestamps.
Disadvantage: Circular references can cause performance issues, increase calculation time, and may not be allowed in certain Excel versions or settings. It can also lead to undesired recalculation loops and may require careful handling.

4. Inserting Current Time with VBA

Yes, you can use the VBA code to insert the current time in Excel. In this section, you are going to insert the current time using VBA. It will give you the freedom to use valid time and date as we like.

Now, to run the VBA code, we have to select Developer >> Visual Basic.

Here, you will go to the Insert tab and select Module.

Now you are going to write the code below 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

🔎 Code Explanation

» Sub TimeStamp()

This line starts the definition of the VBA subroutine called “TimeStamp”.

» Dim s As Date

This line declares a variable named “s” of the Date data type. However, the variable “s” is not used in the code and can be removed.

» With Selection

This line starts a “With” statement, which allows performing a series of actions on a specific object (in this case, the selected range).

» .Value = Now

This line assigns the current date and time to the “Value” property of the selected range. The “Now” function returns the current date and time.

» .NumberFormat = “m/d/yyyy h:mm:ss AM/PM”

This line sets the number format of the selected range to display the timestamp in the format of “m/d/yyyy h:mm:ss AM/PM”. This format represents the month, day, year, hour (in 12-hour format), minute, second, and AM/PM indicator.

Finally, you get the timestamp after running the VBA code.

Why Should You Use This Method?

Advantage: It allows for customization and flexibility in determining where and when to insert the timestamp. It can be triggered automatically based on specific events or conditions.
Disadvantage: It requires knowledge of VBA programming and may require enabling macros or using specific events to trigger the timestamp insertion.

5. Creating a User-Defined Function to Insert Timestamp

You may also create a user-defined function to insert a timestamp. Using a user-defined function helps us to create customized timestamps as the user wants.

Here we use the Time_Stamp user-defined function to get the current timestamp.

To get this, 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

🔎 Code Explanation

» Function Time_Stamp(Reference As Range)

This line declares a VBA function named “Time_Stamp” that takes a single argument called “Reference,” which is expected to be of type “Range.” The purpose of this function is to return a timestamp value.

» If Reference.Value <> “” Then

This line checks if the value of the referenced cell (specified by the “Reference” argument) is not empty. The comparison <> checks for inequality.

» Time_Stamp = Format(Now, “dd-mm-yyyy hh:mm:ss”)

If the referenced cell is not empty, this line assigns a formatted timestamp string to the function’s return value. The Now function is used to retrieve the current date and time, and the Format function is used to format it as “dd-mm-yyyy hh:mm:ss.”

» Else

Time_Stamp = “”

In the “else” part, this line assigns an empty string to the function’s return value.

As a result, you will get the timestamp.

What Are the Advantages and Disadvantages of This Method?

Advantage: It provides a reusable function that can be used in multiple cells or worksheets. It allows for customization and flexibility in formatting the timestamp.
Disadvantage: The function may not update automatically like formulas triggered by worksheet recalculation. The timestamp may not update unless you recalculate or trigger the function explicitly.

How to Use Timestamp in Excel?

You can use timestamps to get hours, minutes, seconds etc. Besides you can get specific dates and time using timestamps in Excel.

1. Inserting Current Time as Dynamic Value

To insert current time as a dynamic value you will need to insert NOW and INT functions. Before we have given the details of the NOW function and here, the INT function overview is given.

Here you have to use the NOW function and change the formatting to Number. Here you can copy the formula below.

=NOW()

Then you get the dynamic value according to the timestamp.

2. Converting Timestamp to Date

Here you will have to use the same NOW function and change the format of C5 cell to Date after using the function.

3. Removing Time from Timestamp

In this section, you have to use INT, DATE functions to remove time from timestamp. We will give a brief overview of the DATE function.

Here you will be able to separate the date from the hour from the timestamp.
First, you have to format the C5 cell into Date format.
You have to use the formula in C5. The formula is given below.

=INT(B5)

This formula =INT(B5) returns the date part from the B5 cell.

Now you will have to use the formula in the D5 cell after formatting the cell as Date. The formula is given below.

=DATE(YEAR(B5),MONTH(B5),DAY(B5))

🔎 Formula Explanation

The formula =DATE(YEAR(B5), MONTH(B5), DAY(B5)) is used to construct a new date by extracting the year, month, and day components from an existing date in cell B5. Let’s break it down:

YEAR(B5): This function retrieves the year component from the date in cell B5. It extracts the year value as a four-digit number.
MONTH(B5): This function retrieves the month component from the date in cell B5. It extracts the month value as a number ranging from 1 (January) to 12 (December).
DAY(B5): This function retrieves the day component from the date in cell B5. It extracts the day value as a number ranging from 1 to 31, depending on the month.

The DATE function takes these extracted year, month, and day values as arguments and constructs a new date using them. The resulting date will have the same year, month, and day as the original date in cell B5.

4. Getting Hours, Minutes, and Seconds from Timestamp in Excel

You will have to use time functions to separate timestamps to get hours, minutes, and seconds. There is a brief overview of time functions in Excel. They are necessary to separate attributes like hours, minutes, and seconds from timestamp.

In this section, you will see how to separate every part of the timestamp by using the respective functions to get hours, minutes, and seconds of cell B5 respectively.

=HOUR(B5)

=MINUTE(B5)

=SECOND(B5)

5. Inserting Specific Time in Excel

Here you will have to use the TIME function in Excel to insert specific time in Excel.

Here you will need to use a formula to turn the values into time. The formula is

=Time(B5,C5,D5)

The formula will return time accordingly.

6. Inserting Timestamp in Excel When Cell Changes

Here, you will have to use a VBA code in the worksheet to insert a timestamp when the cell value changes.

Here you have to give the code only to the Worksheet not to the Module. To do this, right-click on the worksheet and select View code.

Now, write the code given below. You can also copy the code from here.

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

🔎 Code Explanation

» 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

We used these lines to declare variables to use in the code. CellCol represents the column number where the change is being detected, TimeCol represents the column number where the timestamp will be recorded, Row represents the row number where the change occurs, Col represents the column number where the change occurs, DpRng is a Range object used to store the dependents of the changed cell, Rng is used to iterate through the dependents, and Timestamp is a string variable that will store the current timestamp.

» CellCol = 2

TimeCol = 3

Row = Target.Row

Col = Target.Column

These lines assign values to the variables. CellCol is set to 2, representing column 2 (B). TimeCol is set to 3, representing column 3 (C). The row is set to the row number of the changed cell, and Col is set to the column number of the changed cell.

» If Row <= 4 Then Exit Sub

This line checks if the change occurs in row 4 or earlier. If it does, the code exits the subroutine using Exit Sub. This is done to ignore any changes that occur in the first 4 rows of the worksheet.

» Timestamp = Format(Now, “DD-MM-YYYY HH:MM:SS AM/PM”)

This line assigns the current date and time to the Timestamp variable. The Format function is used to format the date and time in the desired format.

» If Target.Text <> “” Then

This line checks if the changed cell is not empty. It ensures that the code proceeds only if there is text in the changed cell.

» If Col = CellCol Then

Cells(Row, TimeCol) = Timestamp

If the changed cell is not empty and the column of the changed cell (Col) is equal to CelllCol(which was set to 2), then it sets the value in the cell at the same row but in the TimeCol (which was set to 3) to the generated 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

If the changed cell is not in column 2 cellcol, it temporarily turns on error handling to prevent any errors caused by dependencies, then it loops through the dependents of the changed cell (Target). If any dependent cell is in column 2, it sets the value in the corresponding row and column 3 (Timecol) to the timestamp. After the loop, it turns off error handling.

Finally, if you change any Data Entry column it will show the time in the Timestamp column.

Which Things Should You 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

1. 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.

2. 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.

3. 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).

4. 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.

5. 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.

Conclusion

In conclusion, inserting timestamps in Excel can significantly enhance data tracking and analysis capabilities. This article presented five different methods, each catering to different needs. Keyboard shortcuts provide quick timestamps but require manual input. Using Excel functions like NOW() and TODAY() offers real-time updates but still necessitates manual intervention. Circular references with IF and NOW functions automate timestamps but may have performance drawbacks. Employing VBA code allows for customization and automation, giving precise control over timestamp triggers. Lastly, user-defined functions provide a balance of flexibility and reusability. The choice of method depends on the specific requirements and preferences, allowing users to efficiently manage time-dependent data in Excel.

Timestamp in Excel: Knowledge Hub

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

Get FREE Advanced Excel Exercises with Solutions!

How to Insert and Use Timestamp in Excel?

How to Insert Timestamp in Excel?

1. Using Keyboard Shortcuts to Insert Timestamp

2. Using NOW and TODAY Functions to Insert Timestamp

<img loading="lazy" decoding="async" class="aligncenter wp-image-385180 size-full" src="https://www.exceldemy.com/wp-content/uploads/2023/06/2Using-Now-and-today-functions.png" alt="Using Now and today functions" width="506" height="250" />

3. Inserting Timestamp Automatically by Applying Circular Reference

4. Inserting Current Time with VBA

<img loading="lazy" decoding="async" class="aligncenter size-full wp-image-385198" src="https://www.exceldemy.com/wp-content/uploads/2023/06/4.1Timestamp-using-VBA.png" alt="Timestamp using VBA" width="280" height="252" />

5. Creating a User-Defined Function to Insert Timestamp

How to Use Timestamp in Excel?

1. Inserting Current Time as Dynamic Value

2. Converting Timestamp to Date

3. Removing Time from Timestamp

4. Getting Hours, Minutes, and Seconds from Timestamp in Excel

5. Inserting Specific Time in Excel

6. Inserting Timestamp in Excel When Cell Changes

Which Things Should You Remember?

Frequently Asked Questions

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

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

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

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

5. Can we customize the timestamp update frequency in Excel?

Conclusion

Timestamp in Excel: Knowledge Hub

Leave a reply Cancel reply