A few years back, when I was working for a tech company, I was one of those people who were an 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 in 5 different ways, and we will try to figure out which is the best 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, one after the other. Here are the steps:

- First of all, select the cell where you need to insert a timestamp.
- After that, use the shortcut key Control + ; (Press and hold Control and then press the semicolon). Once you press this, it will insert the current date (according to your system) in the cell.
- At this time, your cell is in edit mode. Press the Space bar to add a space after the date.
- Now, press Control + Shift + : (Press and hold the Control and Shift keys and then press the semicolon key, which gives the colon). It inserts the current time.
- 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 the date or only the time, just skip the other shortcut key. On a Mac, use Control + ; for the date and Command + ; for the time.
PROs | Cons |
|---|---|
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 the 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 the date and time returned by NOW is mm/dd/yyyy hh:mm (it follows your system’s date settings). But for some reason, if you want a custom format, you can 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 the input box ➜ Click OK.

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

Pros
- It’s a dynamic method.
- 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
- Even though it’s a dynamic method, both functions are volatile, so they get updated whenever you make changes to your worksheet.
- 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 a circular reference to insert a timestamp.
But before you learn this method, let’s understand what a circular reference is all about. Let’s say you have a value of 5 in cell A1 and a value of 10 in cell B1. Now, if you enter the formula =A1+B1+C1 in cell C1, Excel shows a circular reference warning.

This is because you are using cell C1 as a reference in cell C1. When a circular reference happens, there is a never-ending loop in the cell: the value of cell C1 depends on cell C1, and cell C1 depends on its own value.

But when a circular reference is entered, Excel doesn’t calculate it, and the never-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 the iterative calculation option:
- Go to File ➜ Options.
- In the Excel options, select Formulas.
- In the Calculation options section, check the Enable iterative calculation option.
- Click OK.

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

Now, when you enter any value in cell A2, the formula in cell B2 will return a timestamp. As the formula refers to its own cell (B2), it keeps the first timestamp and doesn’t change it later, even when Excel recalculates. If you clear A2, the timestamp goes away. Format column B with a date and time format, as the formula returns the date and time as a number.
Keep in mind that iterative calculation applies to the whole Excel application, so it also hides real circular reference mistakes in your other formulas.

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 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 to the QAT (quick access toolbar) and run it every time whenever you need to add a timestamp.

Here are the steps:
- Add this code to a module by opening the VBA editor.

- After that, go to File -> Options -> Quick Access Toolbar.
- Choose “Macros” from the “Choose commands from” drop-down.
- After that, add the timeStamp macro to the QAT.

- Now, select that macro and click the Modify button.

- Select an icon and click OK.
Now you have an icon on the 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 cell has a value in it. Please follow the below steps:
- Go to the Developer tab and open the VBA editor.
- In the VBA editor, insert a new module and paste this code into it.
- Now, close the VBA editor and come back to your worksheet.
- In cell B2, enter the formula (formula below this list).
- Now, when you enter any value in cell A2, cell B2 will get a timestamp.
=Timestamp(A2)
A few things to know about this function: it returns the date and time as text (because of Format), so you can’t use it in date calculations; it updates again whenever A2 changes; and it can also update when you force a full recalculation (Ctrl + Alt + F9). Also, VBA names are not case-sensitive, so put the timeStamp macro and the Timestamp function in different modules, or rename one of them, to avoid the “Ambiguous name detected” error.
5. Automatic Timestamp with a Worksheet Change Event
This is the method I suggest when you want a timestamp that is added automatically and never changes. The code runs every time you change a cell in A2:A1000 and adds the current date and time in the next cell of column B. If you clear the cell in column A, it clears the timestamp too.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Intersect(Target, Me.Range("A2:A1000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each c In Intersect(Target, Me.Range("A2:A1000"))
If c.Value = "" Then
c.Offset(0, 1).ClearContents
ElseIf c.Offset(0, 1).Value = "" Then
c.Offset(0, 1).Value = Now
c.Offset(0, 1).NumberFormat = "dd-mm-yyyy hh:mm:ss"
End If
Next c
Application.EnableEvents = True
End Sub
- First of all, right-click the sheet tab and click View Code.
- After that, paste the code into the code window of that sheet (not in a normal module).
- In the end, save the file as a macro-enabled workbook (.xlsm).
Unlike the NOW function, this timestamp is a real value, so it doesn’t change when Excel recalculates, and you don’t need iterative calculation.
Which Method to Use
- Now and then, by hand: the keyboard shortcuts.
- A live date and time that should change: NOW or TODAY.
- An automatic timestamp that stays fixed, without macros: the circular reference formula.
- An automatic timestamp that stays fixed, with macros allowed: the Worksheet_Change code (the most reliable).
Conclusion
Adding a timestamp is something we often do while working in Excel. And you have 5 different methods to insert it, plus the change event code. If you ask me, I love to use the VBA button on the 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 timestamps, 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.
Frequently Asked Questions
Why does my NOW timestamp keep changing?
NOW is a volatile function, so it recalculates every time the sheet changes. To freeze it, copy the cell and paste it as values, or use the shortcut keys or the Worksheet_Change code instead.
How do I show seconds in a timestamp?
Use a custom format with seconds, like mm/dd/yyyy hh:mm:ss. Note that the Ctrl + Shift + ; shortcut doesn’t add seconds, so the seconds always show 00 for that method.
Why does the circular reference formula show 0 or a warning?
Iterative calculation is turned off. Excel shows the circular reference warning and returns 0 until you turn it on (File ➜ Options ➜ Formulas ➜ Enable iterative calculation). The setting can switch off again when you open the file on another computer, so check it there as well.
If you want to sharpen your existing Excel Skills, check out these Excel Tips and Tricks.
Very helpful. Thanks
Thanks! It worked.
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
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?
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
Ctrl Shift : will do just the time. They don’t have to go together 😉
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?
When I’m trying the same formula at Spreadsheet, it’s not working please help me with the formula to apply in Spreadsheet
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
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?
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?
Having the exact same issue. Can this be done?
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
great job buddy
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!
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?
The formula didn’t paste correct. Here is the correct formula =IF(A2″”,IF(B2″”,B2,NOW()),””)
Excellent detailed description for TS! Great Sharing!!. Thank You,
Great job Puneet
You’re welcome. 🙂
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
Great ! It is so useful. Thanks buddy.
You’re welcome. 🙂
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?
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.
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?
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.
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.