How to Automatically Insert Timestamp Data Entries in Excel (5 Methods)

This tutorial will illustrate the methods of how to automatically insert timestamp data entries in Excel. When we need to keep track of activities, we use a timestamp. For instance, we’d like to keep track of things like when each employee arrives at the office. Keeping track of timestamp entries helps a person to manage his daily work properly. So let’s see how we can insert timestamp data entries automatically in Excel.

Download Practice Workbook

You can download the practice workbook from here.

5 Easy Methods to Automatically Insert Timestamp Data Entries in Excel

Throughout this article, we will discuss several methods to automatically insert timestamp data entries. The methods of this article will encapsulate both static and dynamic methods. In this article, we will demonstrate 5 different methods to automatically insert a timestamp.

1. Use Keyboard Shortcuts to Automatically Insert Timestamp Data Entries in Excel

Keyboard shortcuts are a static way to automatically insert timestamp data entries in Excel. We can quickly insert a timestamp using keyboard shortcuts. Follow the below steps to perform this action.

STEPS:

• First, select cell C5 where we want to enter a timestamp.
• Next, press Ctrl + ;.
• The above action returns the current date in cell C5.
• Then, select cell C6 and press Ctrl + Shift + :. It returns the current time.
• After that, select cell C7 and press (Ctrl + ; ) + Space + (Ctrl + Shift + ; ).
• Finally, we can see the date and timestamp in cell C7.

Read More: How to Insert Excel Timestamp When Cell Changes Without VBA (3 Ways)

2. Insert Timestamp Data Entries Automatically with Formula

Using formulas is the dynamic way to automatically insert timestamp data entries in Excel. In this method, we will use the TODAY function and the NOW function. The formulas will update automatically with the current date and time.

2.1 Insert TODAY Function

To use the TODAY function follow the below steps.

STEPS:

• Firstly, select cell C5.
• Secondly, type the following formula in that cell:

• Press Enter.
• Thirdly, we can see the current date in cell C5.

• Furthermore, right-click on cell C5.
• Select the option Format Cells.

• A new dialogue box named Format Cells will appear.
• Then, go to the Number tab from that dialogue box. Select option Date from the Category section.
• Afterward, from the type ‘3-4-12 1:30 PM’.
• So, we can see a demo of our output in the sample box.
• Now, click on OK.

• Lastly, we get a timestamp of the current date and time in cell C5.

2.2 Apply NOW Function

Similar to the previous method we can use the NOW function to automatically insert timestamp entries. The NOW function in Excel returns the current date and time. Follow the below steps to do this method.

STEPS:

• In the first place, select cell C5.
• Next, type the following formula in that cell:

• Now, press Enter.
• Finally, we can see a timestamp data entry in cell C5.

Read More: How to Insert Timestamp in Excel When Cell Changes (2 Effective Ways)

3. Enter Data in Different Columns to Insert Timestamp Automatically

Assume that we wish to know each employee’s entry time at an office. Also, the employees will record their entrance timestamps every day by typing their names into a spreadsheet column. When people enter their names in the first corresponding column, a column next to it will automatically display their entry timestamps as well as the dates. To perform this method, we will use the combination of the IF and NOW functions. So, just follow the below steps to implement this method.

STEPS:

• To begin with, select cell C5 and type the following formula:

• Press Enter.
• In addition, drag the Fill Handle tool from cell C5 to C8. This will implement the above formula in the selected cells.

• Next, go to the File tab.

• Select Options from the menu.

• A new dialogue box named Excel Options will appear.
• Furthermore, select the option Formulas.
• Then, check the option ‘Enable iterative calculation’ from the Calculation options.
• Now, click on OK.

• After that, in cell B5 type the name of any employee.
• As a result, we automatically get the date and timestamp in cell C5.

• Lastly, type the name of other employees. We will get results like the following image.

Read More: How to Create Data Entry Form in Excel (Step by Step)

Similar Readings

• How to Convert Unix Timestamp to Date in Excel (3 Methods)
• Convert Active Directory Timestamp to Date in Excel (4 Methods)
• How to Convert SAP Timestamp to Date in Excel (4 Ways)
• Create Data Entry Form in Excel VBA (with Easy Steps)
• How to Create a Fillable PDF from Excel (2 Suitable Ways)

4. Automatically Insert Timestamp Data Entries Using Excel VBA

Suppose you are an advanced Excel user and Familiar with VBA. There is another way to automatically insert timestamp data entries in Excel with VBA. We will use the following dataset to illustrate this method.

Let’s follow the below steps to execute this method.

STEPS:

• First, right-click on the worksheet tab named VBA and select the option View Code.

• The above command will open a VBA code window.
• Next, type the following code in that blank VBA code window.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim zInt As Integer
Dim zStr As String
Dim yzStr As String
On Error Resume Next
zStr = "B"
yzStr = "C"
If (Not Application.Intersect(Me.Range(zStr & ":" & zStr), Target) Is Nothing) Then
zInt = Target.Row
Me.Range(yzStr & zInt) = Format(Now(), "mm/dd/yyyy hh:mm:ss")
End If
End Sub

• Then, click on the Run button or, press the F5 key to run the code.

• Moreover, create a macro named VBA1 and click on the RUN button.

• After that, enter an employee’s name in cell B5.
• As a result, we automatically get the timestamp of entry time in cell C5.

• In the end, type names in the other two cells. We will get their corresponding timestamps like the following image.

Read More: Excel VBA: Insert Timestamp When a Macro Is Run

5. Insert Timestamp Automatically in Excel with Custom Function

This method is another use of VBA code to automatically insert timestamp data entries. In this method, we will create a custom function with VBA code. That function will allow us to enter a timestamp of any entry in our worksheet. We will use the following data set to illustrate this method. We will make entries in column B.  The code will return a corresponding timestamp in column C.

Go through the following steps to do this.

STEPS:

• Firstly, go to the Developer tab.
• Secondly, select Visual Basic from the ribbon.

• A VBA project window will open.
• Thirdly, right-click on Sheet6 (Custom). You will select the worksheet where you want to apply the code.
• Then, Select Insert > Module.

• A blank VBA code window will open.
• Furthermore, type the following code in that code window:

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

• Now, click on the Save button to save the code.

• Afterward, select cell C5.
• Insert the formula with the newly created function in that cell:

• Press Enter.
• Then, drag the Fill Handle tool from cell C5 to C7.

• Subsequently, enter a name in cell B5.
• As a result, we get a timestamp for cell B5 in cell C5.

• Lastly, enter the other two entries. So, we will get results like the following image.

Read More: How to Insert Excel Date Stamp When Cells in Row Are Modified

Conclusion

In conclusion, this article shows 5 different methods to automatically insert timestamp data entries in Excel. Download the sample worksheet given in this article to put your skills to the test. If you have any questions, please leave a comment in the box below. Our team will try to reply to your message as soon as possible. Keep an eye out for more inventive Microsoft Excel solutions in the future.

Related Articles

• How to Insert Static Date in Excel (4 Simple Methods)
• Convert Timestamp to Date in Excel (7 Easy Ways)
• How to Remove Timestamps from Date in Excel (4 Easy Ways)
• Create an Excel Data Entry Form That Includes Checkboxes
• Populate an Excel Spreadsheet from a Web Form
• Create Data Entry Form with Drop Down List in Excel (2 Methods)
• How to Create an Order Form in Excel (with Useful Steps)