You can download the example workbook for this tip here
What’s A Timestamp?
Under lots of circumstances, would you enter the date on a spreadsheet for an entry onto an invoice, a project plan etc and you would do this by entering the current date and time. This however is usually done manually by the user, and takes way too much time for us wanting to Excel At Excel.
We probably all know that we can use the =TODAY() formula or the =NOW() formula to enter a date into Excel, which update automatically when – this is known as “volatile” as the function recalculates when Excel updates.
This is not really any good for a timestamp, as we would want an invoice entry or project update to be static. We therefore can use
CTRL+ ; to enter the current date or
CTRL+SHIFT+: to insert the current time
These both will remain static, but are still very manual having to insert the date and/or time with keyboard the above shortcuts. That’ all well and good but wouldn’t it be great to just insert your changes to a worksheet and the timestamp appear as you type?. You can but it takes a bit of Excel magic and an understanding of Circular Formulas.
What’s this about Circular Formulas?
You may have come across them before but if not here’s a quick run down.
A circular formula is one that refers back to itself either directly or indirectly. Let’s take a look at an exmaple.
Lets look at a direct example.
If I type =A4+1 in cell A4 I am directly referring to the cell I am writing the formula in. This results a warning from Excel in case you did not create this circular reference intentionally (as we did) and the result of the formula is zero.
Let’s look at an indirect example
If I type 2 into cell A1,
then type =A1 into cell A2
then A2 refers back to A1 and gives a result of 2 which is a normal formula.
If I then go back to cell A1 and enter the formula =A2+3, this will create a circular formula as A2 is based on A1 and I am writing the formula in A1 so essentially the formula is referring to itself.
You can see above that the circular reference is indicated by Excel with blue vertical line after selecting OK to it’s warning.
Ok so let’s take this one step further…..
There is a setting in Excel called ‘iterations’, which tells it how many times to recalculate a formula. Without this function, Excel would go into an infinite loop and eventually stall. By default this is turned off in Excel which is when when you type a circular reference , Excel throws you a warning and doesn’t calculate the formula as it could run infinitely.
If we turn on the iterations function
1. Office button
2. Excel Options
5. Change the default iteration to 1, so it recalculates one time before it stops.
In our case this will result in cell reference A1= 5
So, at the start of this post we were to insert time stamps into Excel, we can exploit this circular referencing and iteration to our full advantage based on the theory above to insert our required time stamps.
Let’s go ahead and do that- in a scenario that we want the timestamp to be in Column A when we are entering data into Column B
1. Type the following formula into A1
2. Type something into Column B
3. Your date and time will be stamped.
4. Drag the formula in Column A down to allow time stamping all down your column.
Isn’t that just the bees knees?
An explanation of what the formula is doing.
The formula is checking cell B1, and if it is not empty it runs the circular formula =IF(A1=””,NOW(),A1) which returns the NOW() function if A1 does not already have a value- thus stamping the time.
Go ahead and give this a go, let me know if you have any other way of creating a time stamp in Excel?