How to Insert Timestamp in Excel (6 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Insert Timestamp in Excel


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.
=NOW()
  • Press ENTER.

Using NOW Function to Insert Timestamp

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

Copied Values only

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

4 NOW function update Timestamp automatically


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.

Applying Keyboard Shortcut for Including Static Timestamp

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

Showing Time format


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.

Write VBA code in Excel Sheet

  • Now write the following code in the sheet.

VBA Code to get the instant time with update

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

Code Breakdown

  • Here, in this code, Target.Column = 4 denotes while you are changing any values of the D (4 number) column in Excel. So, you have to change the column number based on where you keep the data.
  • Target.Offset(0,1) denotes the output (timestamp) that will store in the E column (next to the cell of the D column). So, you may change it to your preferred one.

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

Applying VBA to Insert Timestamp with Second in Next Cell

Note: This method will provide the seconds of time too. But you can see the seconds of the inserted timestamp in Formula Bar.

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.

Create Function with VBA for Timestamp

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

Code Breakdown

  • Here, this code will create a function named insert_Time_Stamp.
  • In this function, there will be one argument which should be a cell.
  • This insert_Time_Stamp function will return the current date and time if the given cell contains any value otherwise it will return “No value in D column”.

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

Using a Defined Function to Place Timestamp


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.

Basic VBA code for Timestamp

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.

Inserting Button to run a particular code

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

Assign macro to the Button

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

Applying VBA with Button to Put Timestamp in Excel


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.

Enabling Iterative Calculation in Excel

  • Now, enter the following formula in the E5 cell.
=IF(D5<>"",IF(E5<>"",E5,NOW()),"")
  • 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.

Combining Excel IF & NOW Functions to Insert Timestamp

Formula Breakdown

  • The 1st IF function says whether the D5 cell is valued or blank. If the cell contains any value, then the 2nd IF function will work.
  • The 2nd IF function will check whether the E5 cell is filled or not. If the cell is filled, then the IF function will return the cell value of E5.
  • Otherwise, it will operate the NOW function. Which gives the current time and date.


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.

Formatting Date

  • Finally, this will convert the time to date.

Converting Timestamp 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.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

4 Comments
  1. Thank you very much. Really detail and precise.
    All the best
    Rob

  2. This was an excel-lent instructions, thank you very much

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo