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
| Delicious | Stumble it |
« Prev | Home | Sales Funnel Charts in Excel - Tutorial »
Have an Excel Question?
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
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
Join Our Community
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…