By Puneet Gogia
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 completing 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.
...it’s a combo shortcut.
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:
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.
...a simple dynamic method.
If you want to use a formula to insert a timestamp, the perfect way is to use NOW function.
When you enter this function in a cell it returns 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 custom format option.
Select the cell ➜ Press shortcut key control + 1 ➜ Select “Custom” ➜ Enter “mm/dd/yyyy hh:mm” in input box ➜ Click OK.
If you want to get into an advanced method and don’t want to use method #1 and #2 then you can use circular reference to insert a timestamp.
But before you learn this method let’s understand what actually 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.
This is because you are using cell C1 as reference in the cell C1.
When 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 value of A3 is dependent on reference to cell A3.
But when a circular reference is entered, Excel don’t calculate it and the non-ending loop never start.
Here’s the deal:
You can enable “iterative calculation option” to force Excel to preform the calculation at least for one time and use NOW function in the calculation.
This way Excel will update the cell formula only for one time instead of every time.
Steps to enable iterative calculation option:
Now when you enter any value in cell A2 the formula in cell B2 will return a timestamp.
Dim ts As Date
.Value = Now
.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
To use this code you can add it on QAT (quick access toolbar) and run ever time whenever you need to add a timestamp.
Here are the steps:
Now you have a icon on QAT and whenever you need a timestamp you can select the cell and click this button to insert it.
Yes, you can also create a UDF 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")
Timestamp = ""
By using this user defined function you can get a timestamp in a cell if another has a value in it.
Please follow below steps:
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 VBA button on QAT for this. The best way is to add this code in a personal macro workbook so that you can use it in all the workbooks.
This is the whole story about a timestamp and I’m sure you found it useful but now tell me one thing.
Do you know any other method to 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.