How to Insert a Timestamp in Excel [Formula + VBA + Shortcut]

HomeExcel BasicsHow to Insert a Timestamp in Excel [Formula + VBA + Shortcut]

A few years back when I was working for a tech company I was one of those people who were Excel help point for all. And that’s the real reason I’m fascinated to learn more. One day the lady who was working as a reception coordinator came to me and asked:

Puneet, I’m managing a list of tasks and I want to add date and time in the corresponding cell on completion of each task. Which is the best way?

And quickly I realized that she was talking about a timestamp. I’m sure you also use it while working in Excel. In general, it contains the current date and time, and we use it to capture the completion time of a task. Now the thing is: Which is the best way to insert a timestamp in Excel?

In this post, you’ll learn how to create a timestamp in Excel using 5 different ways and we will try to figure out this is the best out of all. So let’s get started.

1. Using a Keyboard Shortcut to Insert a Timestamp

There are two different shortcuts to insert a date and a time. And, here we need to use both of them subsequently. Here are the steps:

insert a timestamp in excel using a shortcut key
  1. First of all, select the cell where you need to insert a timestamp.
  2. After that, use the shortcut key Control + : (Press and hold control and then press colon). Once you press this, it will insert the current date (according to your system) in the cell.
  3. At this time, your cell is in edit mode.
  4. Now, press Control + Shift + : (Press and hold control and shift key and then press colon).
  5. Your cell is still in edit mode, now press the enter key to complete the entry.

In short, you need to press two shortcuts in sequence to insert this. And, if you want to add only one thing out date and time, just skip the shortcut key that.

PROsCONs
If you want to save time and have fewer cells, this method is perfect.This is not a dynamic method, you have a static timestamp. And if you want to update the time stamp you need to enter it again.
When you enter both date and time, Excel automatically picks the right format to display it.You need to press two different shortcut keys to enter it.

2. Insert a Timestamp with NOW Function

A simple dynamic method. If you want to use a formula to insert a timestamp, the perfect way is to use the NOW function. When you enter this function in a cell it returns the current date and time according to your system’s settings.

The default format of date and time return by NOW is mm/dd/yyyy hh:mm. But for some reason, if you want a custom format, you change its format using the custom format option. Select the cell ➜ Press shortcut key control + 1 ➜ Select “Custom” ➜ Enter “mm/dd/yyyy hh:mm” in input box ➜ Click OK.

And if you want to enter the only date then you can use TODAY instead of NOW, it only returns the current date according to the system’s settings.

insert a timestamp in excel with today

Pros

  1. It’s a dynamic method.
  2. You can use both of the functions with an IF function to create a condition to enter a timestamp if another cell has a value.

Cons

  1. Even it’s a dynamic method but as both of the functions are volatile they will get updated whenever you make changes in your worksheet.
  2. And if you just want values instead of formulas you need to convert them into values manually.

3. Using Circular Reference for Creating a Timestamp

If you want to get into an advanced method and don’t want to use methods #1 and #2 then you can use circular reference to insert a timestamp.

But before you learn this method let’s understand what circular reference is all about. Let’s say you have value 5 in cell A1 and value 10 in cell B1. Now if you enter a formula =A1+B1+C1 in cell C1, it will return a message circular reference error.

insert a timestamp in excel with circular reference

This is because you are using cell C1 as a reference in cell C1. When a circular reference error happens, there is a non-ending loop in the cell. Like: reference in cell A3 is dependent on the value of cell A3 and the value of A3 is dependent on reference to cell A3.

insert a timestamp in excel with circular reference loop

But when a circular reference is entered, Excel doesn’t calculate it and the non-ending loop never starts.

Here’s the deal:

You can enable the “iterative calculation option” to force Excel to perform the calculation at least one time and use the now function in the calculation. This way Excel will update the cell formula only one time instead of every time. Steps to enable iterative calculation option:

  1. Go to File ➜ Options.
  2. In the Excel options, select Formulas.
  3. In the Calculated options, check the Enable iterative calculation option.
  4. Click OK.
insert a timestamp in excel with circular reference activate iteration

After that in the cell B2, enter below formula in the formula bar: =IF(A2<>””,IF(B2<>””,B2,NOW()),””)

insert a timestamp in excel with circular reference enter formula

Now when you enter any value in cell A2 the formula in cell B2 will return a timestamp.

insert a timestamp in excel with circular reference enter value

4. VBA to Add a Timestamp

If you are a VBA freak then I’m sure you’ll find this VBA code useful. With this, you don’t need to enter a formula or even not use any shortcut key. Just select the cell where you need to enter a timestamp and run the macro.

Sub timeStamp()
Dim ts As Date
With Selection
.Value = Now
.NumberFormat = “m/d/yyyy h:mm:ss AM/PM”
End With
End Sub

How to use this code

To use this code you can add it on QAT (quick access toolbar) and run it every time whenever you need to add a timestamp.

Here are the steps:

  • Add this code in module by opening VBA editor.
    insert-a-timestamp-in-excel-vba-code-in-module
  • After that, go to File -> Options -> Quick Access Toolbar.
  • Choose “Macros” from “Choose Command from” drop down.
  • After that, add timeStamp macro to QAT.
    insert-a-timestamp-in-excel-vba-code-add-qat
  • Now select that macro click modify button.
    insert-a-timestamp-in-excel-vba-code-add-qat-icon
  • Select an icon and click OK.

Now you have an icon on QAT and whenever you need a timestamp you can select the cell and click this button to insert it.

4.1 Using UDF for Timestamp

Yes, you can also create a custom Excel function for inserting a timestamp in Excel. Below is the code for this UDF.

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

By using this user-defined function you can get a timestamp in a cell if another has a value in it. Please follow the below steps:

  • Go to Developer tab and open VBA editor.
  • In VBA editor, insert a new module and paste this code into it.
  • Now, close VBA editor and come back to your worksheet.
  • In the cell B2, enter below formula.
    insert-a-timestamp-in-excel-vba-function
  • Now, when you enter any value in cell A1, cell B1 will get a timestamp.

Conclusion

Adding a timestamp is something we often do while working in Excel. And, you have 5 different methods to insert it. If you ask me I love to use the VBA button on QAT for this. The best way is to add this code in a personal.xlsb so that you can use it in all the workbooks. This is the whole story about timestamp and I’m sure you found it useful but now tell me one thing.

Do you know any other method for this?

Please share with me in the comment section, I’d love to hear from you, and please don’t forget to share this tip with your friends.

This tutorial is a part of our Basic Excel Skills, and if you want to sharpen your existing Excel Skills, checkout these Excel Tips and Tricks. But if you are just getting started with Excel, check out this tutorial on Excel Basics.

Must Read

37 thoughts

Leave a Comment

Your email address will not be published.

  1. This keyboard shortcut is very helpful. I can get the time to timestamp, but it will not show the correct number of seconds, just (00) until the minute changes. I have tried changing the time format, but it still does not show the correct seconds. I want to use this to time a 5K down to the second. What am I doing incorrectly that it will not show the actual seconds?

    Thank you!

    Reply
  2. I have tried to copy and paste the formula into the formula bar as instructed, but I get an error message and typing in the cell that is supposed to prompt the time stamp results in nothing. What is missing?

    Reply
  3. Process improver here: I LOVE METHOD 3! That way you can have two collumns: 1. What is the process doing? 2. until when.
    In the second Collumn you put the formula and in the first collumn you just type what you observe during a process observation.

    Reply
  4. I like the timestamp idea but how can I use this code
    Sub Timestamp()

    Dim ts As Date

    With Selection

    .Value = Now

    .NumberFormat = “m/d/yyyy”

    End With

    End Sub

    but instead insert say my name when I click the quick access button.

    Reply
  5. Hi, need your help on this.
    I’m currently below code and sometimes it works, sometimes it doesn’t.
    There were a few times, where the date have already been generated example
    Case 123 was updated on 11/01/21, marco captures and populate the data out on the cell.
    21/01/20 I enter the file, Case 123 changes to 21/01/20 without me changing anything.
    Is there anyway to stop it from changing once the data is recorded?

    Working on a sharedfile.
    ——————————————————————————————————–
    Created this under module > General > Declarations.

    Sub SaveAsShared()
    ActiveWorkbook.SaveAs , , , , , , xlShared
    End Sub
    Function Timestamp(Reference As Range)
    If Reference.Value “” Then
    Timestamp = Format(Now, “dd MMM yy hh:mm:ss”)
    Else
    Timestamp = “”
    End If
    End Function

    Reply
  6. Thanks for the quick timestamp solution!

    How would I also add a username to that stamp? I need a user to sign off on a particular task with their username and time/date. We are converting from a Google sheet to Excel 365 and want to have the same sign of capabilities.

    Thanks in advance!

    Reply
  7. so i have used a timestamp function UDF and it works great, fills in the date in cell a2 when i enter text into cell a1 but i also have macros that i run off of 2 clickable buttons, but when i run either one of those macros the timestamp (date) updates to todays date. My question is how can i make my timestamp static?
    Below is my timestamp formula

    Function Timestamp(Reference As Range)
    If Reference.Value “” Then
    Timestamp = Format(Now, ” mm-dd-yyyy”)
    Else
    Timestamp = “”
    End If
    End Function

    Reply
  8. You win. I’ve been looking for this quick entry shortcut for a timestamp for a while. THANK YOU! ctrl-shift-: — it’s perfect.

    Reply
  9. Why Microsoft does not provide a simple formula that would produce a static date and time? it’s very useful.

    Reply
  10. Hi ,
    i am trying to add a time stamp to an excel file and all of the vba codes i’ve searched for don’t work ,
    any tips?

    thanks in advance
    George

    Reply
  11. Hi, it has been very helpful but I need to record only the time of a data entry, is it possible to record only the time and not the date with it?
    If yes whats the method?

    Reply
    • Hello Arya,
      If you modify the code to the following, it should just record the time not the date

      Function Timestamp(Reference As Range)
      If Reference.Value “” Then
      Timestamp = Format(Now, “hh:mm:ss”)
      Else
      Timestamp = “”
      End If
      End Function

      Reply
  12. Can I edit the “date stamp” code that is now linked to the quick access toolbar, to change to a “my path code”?
    Or, how do I delete/ remove the “date stamp” code and Icon from the quick access toolbar?

    Reply
  13. When I’m trying the same formula at Spreadsheet, it’s not working please help me with the formula to apply in Spreadsheet

    Reply
  14. Hi Puneet ,
    I have use below UDF Code timestamp is perfect pick now date but issue is format show in text not use date format.
    please can you suggestion how to use this udf code date format.

    UDF Code
    Function Timestamp(Reference As Range)

    If Reference.Value “” Then

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

    Else

    Timestamp = “”

    End If

    End Function

    Reply
    • Hi

      I would like to record the timestamp of a cell and it should be static and should not change if we reopen the excel. Can you please suggest a formula for it?

      Reply
  15. I need a time stamp in each row of a ca. 300 row spreadsheet of user
    information such that the timestamp is automatically updated if any cell in
    its respective row is changed.

    My question: is this possible using Excel?

    Reply
    • Hi Ashutosh,
      Yes its very much possible use the code:
      I found this code in another thread,
      Note : If you want to record the date and time in another column just change the 1 with the column number in offset syntax in given code

      Private Sub Worksheet_Change(ByVal Target As Excel.Range)
      Dim rCell As Range
      Dim rChange As Range

      On Error GoTo ErrHandler
      Set rChange = Intersect(Target, Range(“A:A”))
      If Not rChange Is Nothing Then
      Application.EnableEvents = False
      For Each rCell In rChange
      If rCell > “” Then
      With rCell.Offset(0, 1)
      .Value = Now
      .NumberFormat = “hh:mm:ss”
      End With
      Else
      rCell.Offset(0, 1).Clear
      End If
      Next
      End If

      ExitHandler:
      Set rCell = Nothing
      Set rChange = Nothing
      Application.EnableEvents = True
      Exit Sub
      ErrHandler:
      MsgBox Err.Description
      Resume ExitHandler
      End Sub

      Reply
  16. I was all over the internet trying the find a clean solution for a timestamp. You’ve laid things down very simply and I’m so grateful. Thank you!

    Reply
  17. I’m using this formula =IF(A2″”,IF(B2″”,B2,NOW()),””) I want to use a specific number in column A to make the timestamp appear in column B. Is this possible and if so how can I change the above formula to make it happen?

    Reply
    • The formula didn’t paste correct. Here is the correct formula =IF(A2″”,IF(B2″”,B2,NOW()),””)

      Reply
  18. I have a dynamic worksheet with query and refresh automatically every 5 seconds. I need time stamp for cell that’s has changes in value. I tried above methods, problem when query is refreshed, time stamp is refreshed as well. Is it possible in excel to retain previous time stamp date/time after query is refreshed if there are no changes in value. Hope you can help me with this. Thank you

    Reply
  19. I use macro code to automatically put a date stamp in an adjacent cell when the cell value changes. I am chasing code to put a date stamp into the cell comment when the cell value changes. This will eliminate a column on my spreadsheet. Can you help?

    Reply
  20. Easier way depending on how you use your file is to disable automatic calculations. If it is in manual mode, you enter NOW(), and it is locked in unless the formula is in edit mode and confirmed using the check mark or return.

    Reply
  21. I also need to add a timestamp and can only use the iteration/formula method as we use the file in Sharepoint in the Cloud and that does not allow for VB code or macros.
    I use a lot of if and vlookup and Pivot Tables, will setting itterations to 1 not have a negative influance on these calculations?

    Reply
  22. thanks for your help, but I have one issue coz I want to protect the sheet so that its not editable, how can I do that? when I protect the sheet i get a run time error, thanks for your help.

    Reply
  23. Greetings Puneet! The function you created holds the date format of “dd-mm-yyy hh:mm:ss” which gives the Julian date at the end of the year shown i.e. 12/23/2017257 16:35:21 instead of “dd-mm-yyyy hh:mm:ss” which would show 12/23/2017 16:35:21. Don’t know if you meant to do that but it does show a neat shortcut for it.

    Reply