SubscribeAutomatically insert timestamps in excel sheet using formulas
What is a time stamp?
Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it.
So how do I generate timestamps in Excel?
We all know about the now() formula in excel and how it tells you the current date and time. The only problem with it is that, when you press F9 or recalculate formulas, the now() will be changed to reflect latest date and time. Thus we cannot use NOW() as a time stamp.
A good alternative (although manual) is to use keyboard shortcuts CTRL + ; or CTRL + : to insert current date and time in the active cell. Since this places the value, we don’t need to worry even if the date or time changes. But the only issue is, every time you need a time stamp you have to press the 2 keys.
A better way to get timestamps in excel is to use a special type of formulas, called as circular formulas.
What the heck is a circular formula?
A circular formula refers to itself (or to another cell which in turn refers to this cell). Confused ? Well, let me give you an example.
=if(A2<1000, A2+1,A2)
Now the above formula is normal, but the moment you write it in the cell A2, you have made it a circular formula. So, each time you run the formula (by pressing F9) the value in A2 will go up by 1, just like an incrementing counter.
But here is a thing you should be aware of: There is a setting called “iterations”, it tells excel how many times it should run a formula before finalizing the result.
This setting wouldn’t make any sense in case of regular formulas. But it is very important for circular formulas. If not for this, excel would go in to an infinite loop and stall.
Now, by default iterations in formula computation is turned off. That means when you write a circular formula or create a circular reference, excel shows you a message warning you and it doesn’t evaluate the formula.To turn the iterative calculation, and thus enable usage of circular formulas, go to menu > tools > options > calculation tab and check the iteration box. In excel 2007, you can go to office button > excel options > formulas > iteration area.
As you can see the default max. iterations per formula execution is 100, which means, every time you run the circular formula, excel calculates it 100 times before presenting the value. So our formula =if(A2<1000, A2+1,A2) written in cell A2 would go up by 100 every time you run.
Back to Generating Time Stamps
Now that you are knowledgeable about circular formulas, here is how we can refine the “time stamp generation” problem:
if the cell doesn’t already have time stamp, insert current date time using now(), otherwise leave the value as it is
and we will trigger the formula once we make certain changes, for eg. whenever you enter data, you can tell excel enter timestamps. See this:
The formula itself is very simple:
=IF(C3<>"",IF(B3="",NOW(),B3),"") in cell B3.
What it does?
It checks cell C3 and whenever it is not empty it runs the circular formula IF(B3="",NOW(),B3) which fetches NOW() value only if the cell B3 doesn’t already have a value, thus serving timestamps.
The above formula works only if you have enabled iterative calculation mode as described above.
Your thoughts ?
PS: Having questions on formulas, try our excel formula helper tool and learn 50+ forumals in a fun way
Trackbacks & Pingbacks
- Pingback by Excel Formula Errors - Understand and Debug Them | Excel Howtos | Pointy Haired Dilbert - Chandoo.org on April 30, 2009 @ 5:10 pm
- Pingback by Team To Do Lists - Project Tracking Tools using Excel [Part 2 of 6] | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on August 5, 2009 @ 11:04 am
- Trackback by uberVU - social comments on March 1, 2010 @ 11:06 pm
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Just last night, I was browsing the web looking for how to create a command button that when pressed will update the timestamp on a cell. I have a checklist of names and each name that I find I will mark them off. Cell A1 has the timestamp. Is this posible?
Another great suggestion, but I also would like to change the timestamp in case someones modifies the cell. Is that possible without VBA?
@JacMars: Welcome to PHD and thanks for the comments
You can use a variation above formula to do just that. You can maintain a counter variable in a cell that goes up or changes whenever you check a cell (try using a one of the count() formulas). Once this cell reflects the changes you can link the timestamp formula to it and generate timestamp automatically.
I am not able to give you the exact formula since I couldnt really understand what you meant in the question. Can you elaborate ?
@Oliver: I am not aware of any formulas that can do this. I would love learn something on these lines though. I can figure out a circular formula that can store the first value you have ever entered in a cell but that is not useful in this case.
Let me know if you come across a good solution for this. Our donut policy is alive and kicking
@Oliver, @Chandoo
Here is how to change the timestamp when the data cell is modified. There is probably a cleaner way to do this but I just came up with it now and haven’t gone over it too thoroughly. Note that:
1) this uses two “helper” columns in addition to the Data and Timestamp columns – you can feel free to hide these columns in your final workbook once you have it working, but don’t delete them
2) something is screwy with how Excel is propagating the calculations (at least on my machine), so it’s important that the columns are in the order I describe them here (if anyone can figure out why this should matter, please explain – I would assume that since I have iterations set to 100 and automatic calculation then the order wouldn’t matter, but I guess it does)
Anyway, the idea is to have one cell (DataHelper) which will keep track of the previous data and will therefore be able to tell you when the data has changed. When that happens, another cell (TimeHelper) will update itself with the current time and will store that, and finally the Timestamp cell will pull the time from that TimeHelper cell.
1). Data: (still in cell C3), this is where you enter your data
2). DataHelper: (cell D3), formula: =IF(C3″”,IF(D3=”",0&” – “&C3,IF(RIGHT(D3,LEN(D3)-4)=”"&C3,D3,MOD(LEFT(D3,1)+1,10)&” – “&C3)),”")
The output of DataHelper will be in the form “# – data”, where # is an integer between 0 and 9, and data is the data in C3. Walking through the logic, if Data is not empty, then there are three possible cases. (a) this is the first time data is being entered (which we know because DataHelper is empty), (b) no one modified the data, we’re in a static state (which we know if Data matched the end of DataHelper), or (c) the data was just modified (all other cases). If (a), then just output “0 – data”. If (b), keep DataHelper the same. If (c), then increment #, and update with the current data. [Note: In the comparison IF(RIGHT(D3,LEN(D3)-4)=""&C3 - I wrote ""&C3 to force C3 to be evaluated as text.]
3). TimeHelper: (cell E3), formula: =IF(C3″”,IF(E3=”",0&” – “&NOW(),IF(LEFT(E3,1)=LEFT(D3,1),E3,LEFT(D3,1)&” – “&NOW())),”")
The output of TimeHelper will be in the form “# – time”, where # is an integer between 0 and 9, and time is the time of last modification (don’t be alarmed – the time will appear as a number – we’ll fix this in the Timestamp cell). The logic is almost identical with DataHelper. The main difference is that here we are able to tell the difference between scenarios (b) and (c) based on whether the # prefix here matches the # prefix of DataHelper.
4). Timestamp – (cell F3), formula: =IF(E3″”,VALUE(RIGHT(E3,LEN(E3)-4)),”")
This will extract the time from TimeHelper. Make sure to format this as Time in the standard Excel number formatting dialog.
OK, that should do it. Let me know if that works for you. Any ideas on how to clean this up?
Some of my formulas above are a bit mangled. Wherever you see quotes in the formulas, it’s either an empty string, or a string with 3 characters – space, minus, space.
Hi Chandoo,
I want to a cell to display running time ie. the seconds should be changing. What is the easiest way to do this?
THANKS SO MUCH!! Yes, I’m yelling because I’m so happy to have found this timestamp formula and your explanation of how it works so that I can modify it to fit my needs. I’ve been struggling with a way to do this that wasn’t a complex solution. This is it.
BTW – I love the Calendar, too
Thanks, again. – Jim
@Zach: that is awesome.. Thank you so much for the formula.. It worked just fine
I have actually started out to solve the updation problem like this but gave up after trying for a while.
For those of you, the formula for dataHelper column is
and for timeHelper column is
Here is an e-donut for you Zach…
@All, the formulas are getting cropped, another version here… Just in case
For those of you, the formula for dataHelper column is
=IF(C3<>"",IF(E3="",0&" - "&C3,IF(RIGHT(E3,LEN(E3)-4)=""&C3,E3,MOD(LEFT(E3,1)+1,10)&" - "&C3)),"")and for timeHelper column is
=IF(C3<>"",IF(F3="",0&" - "&NOW(),IF(LEFT(F3,1)=LEFT(E3,1),F3,LEFT(E3,1)&" - "&NOW())),"")@Sumeet: A cell that displays time.. Well, you may want to resolve to a VBA based solution for this. If you are looking a clock that updates automatically, do visit our earlier post on doing clocks in excel: http://chandoo.org/wp/2008/08/19/show-time-excel-fun/
@Jim: You are welcome
Is it possible to create a formula that only runs once? In other words…if i have a formula that gets triggered by a number in cell A3 can I make it so that it only runs once even if the number in cell A3 changes later. I would like to keep a running total in A3 and have several different calcuations off of that same cell so that I can refer to each quater and see what the value was in each.
@Mike: Welcome to PHD and thanks for your comments.
Yes, you can do the same using circular formulas as shown above.
assuming your formula is cell A4 and it depends on A3, you can write something like this in A4
=if(a4="",sum(a3),a4)this formula runs once and then doesnt change no matter what. But you have to enable iterations from calculation options as described above.
That is a very nice tip… In addtion to this, is there a way to populate the user name/ user ID, of who made the entry? That would complete the time-stamp information.
Thanks,
Juwin.
@Juwin: Welcome to PHD.
You can add the user information easily to this. Just create another column with username as the title and use it to enter the userids. If you want to automate this step based on the windows userid, you may have to use VBA.
I wanted to automate the step based on Windows userID. But when I use VBA, everytime I open excel, I get a popup regarding macro security. Evenif I try to lower my security, when others open the excel sheet on their machine, they get the popup, and they have the option to disable macro. Once they disable the macro, then the automatic population logic doesn’t work anymore. That is the reason why I wanted to use a formula, instead of VBA.
Thanks,
Juwin.
@Juwin: hmm VBA in excel means, presenting your audience with the dialog asking them whether they are ok to relinquish the control. One way to get around this is to write a UDF that can get the userid, then publish it as an add-in and ask your colleagues to install it. One time process, but reduces pain there after.
Even simpler one? here is a way. Ask your readers to store the file on Desktop. Then use CELL() function – cell (”filename”) to extract the file location. This shows, a typical windows file path like this: D:\DOCUME~1\{username}\Desktop\[filename.xls]sheetname. Now extract the username from the path by splitting text using some simple text formulas. I know this kinda forces your user to save the file etc, but it can work well.
Any other suggestions you can think of ?
Hi guys,
I have used the formulas supplied by Chandoo on the 15th Jan – Is there any way to make the data counter incremental, i am only getting 1’s – ie:
modification 2 would equal: 2 – New Data
modification 3 would equal: 3 – New Data
Thanks
@Martin: Have you enabled iterations from calculations options of excel.. I know it is stupid question…
Also, when you start out, first you need to clean up the data cells, then start entering values one after another, that way counter increments properly…
I am having trouble with formulas in timeHelper column as displayed by chandoo. I have entered the formulas exactly as written but the problem seems to be that I cannot get the second IF statement to recognize the comma after NOW() so it NEVER gets to the False condition which is the rest of the formula, so consequently it gives an error and never gets past the true condition. Has anyone else had this problem?
Got it! Never mind…
If you can use VBA (and don´t want to activate manual calculation, which has brought serious trouble in my experience when checking big tables due to forgetting to hit F9), this simple procedure will do the trick:
————————–
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(”C9:C1000″), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
‘.NumberFormat = “dd mmm yyyy hh:mm:ss”
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub
————————–
@Mark.. cool
@Kaliman… That is a cool script, thanks for sharing it with us. Btw, you dont need to turn on the “manual mode”. You just need “iterative mode” to enable circular references.
I have a question about the timestamp as well. I am wondering if I were at the time to key something simple like “C”, “X” or “P” in a cell, I would like the cell next to it have the date I actually typed that into the cell and stay at that timestamp date.
I appreciate all the info on this matter already, im just still having difficulties, very long day.
@Jaye… I am sorry, but isnt that exactly what this article is all about?
Ok, I got the time stamp to work. Is there any way of it being able to read what date the cell was originally keyed into?
Thank you for the time stamp formula. I have been looking for something like this for a long time. I have a spreadsheet that is used in a power plant and need the time the readings were entered.
@Jaye.. missed your comment in holiday mood.. Are you still looking for help?
@Mike V: You are welcome
If you dont mind, share with us how this formula is helping you in your work. Others might benefit it from it.
I’m not sure what I’m doing wrong but the Time Stamp formula provided above isn’t working for me: =IF(E3?”,VALUE(RIGHT(E3,LEN(E3)-4)),””)
What am I missing?
@Adrian: Have you enabled “iterative calculation mode” from formula settings?
What a great tutorial! I’m a beginner but this was easy to understand and the instructions for excel 2007 were just what I needed. I have one question – is it possible for the timestamp to display in the same cell as the data? For example, I type the sentence “hello, my name is John” inside of cell A1, and the timestamp shows up right after “John”?
@Mel.. Thanks, I am happy you like this.
And no, we cannot write formulas in such a way. May be you can use VBA.
@Mel:
you can combine cells like:
hello John | 28/01/2010 12:05 | hello John 28/01/2010
cell values:
hello John | now() | =CONCATENATE(A1,” “,TEXT(B1,”dd/mm/yyyy”))
and it’s variations… you can cut name off using wiledcards (more advanced)
I need to do the following in excel. Once the time stamp in a cell A1 = 9:30 am it will record true or false based on certain conditions. Now once A1 cell records true or false at 9:30 I need another cell say B1 to record the A1 result, true or false and remain constant until 9:30 the next day when A1 is recalculated and the results sent to B1 again.