Do you need to insert a timestamp in Excel? This question is one of THE most requests I receive on a regular basis. Although there is no built-in function or process in Excel to insert a timestamp, it is possible to create a timestamp in Excel with formulas and a little tweek to iteration settings in Excel. But more about that later. This article will walk you through the formula to create a timestamp in your Excel worksheets along with a simple-to-follow example. Lets get started!.
When working in Excel under a lot of circumstances, entering dates in Excel is a straightforward process. For example, dates are entered for invoices or a project plan, this would normally be carried out manually. This is very slow and manual, There is a much more efficient way to achieve an excel timestamp.
Ways To Enter Date And Times Into Excel.
There a a few ways to enter dates and times into Excel. I differentiate these from the automatic timestamps. But, to understand the differences in these methods is important. So here are the two ways users normally attempt to enter date and time into Excel. Both of these functions are known as volatile functions. These types of function recalculate every time there is an update in the Excel workbook. This is an important point in this solution.
The NOW Function To Enter Date And Time
The NOW() function in Excel will enter the current date and time from your system clock. The syntax is easy. Type =NOW() and hit enter. It is important to note that Excel stores date and times as numbers, so ensure that your cell formatting is correct to display date and time.
- Right click on your cells
- Select Format cells
- In the dialogue box select “dd-mm-yyyy hh:mm:ss”
- Hit Ok.
As already stated above this function is known as a volatile function. This means that Excel recalculates this function every time there is a change in the workbook. So, just because your enter the NOW() function at the current date and time, it WILL change when the workbook updates. So, as you can see this is probably not the solution to timestamp your Excel workbook.
The TODAY Function To Enter Date Into Excel.
The TODAY() function in Excel will enter the current date inot your Excel worksheet from your system clock. The syntax is easy. Type =TODAY() and hit enter. It is important to note that Excel stores date and times as numbers, so ensure that your cell formatting is correct to display date.
- Right click on your cells
- Select Format cells
- In the dialogue box select “dd-mm-yyyy”
- Hit Ok.
Again, this is a volatile function recalculating everytime Excel updates. This function in will also not give the desired Excel timestamp in Excel.
Using Excel Shortcuts To Insert Date And Time
Ok, so we cannot really use and Excel function currently to create a timestamp in Excel. We can however use a very manual process. Thee are tow shortcuts that insert the current date and the current time. These two shortucts do create a timestamp which is not volatile. however, the process is very manually and the user physically has to insert these shortcuts rather than the timedtamo happening automatically.
here are the shortcuts to try.
- CTRL+ ; to enter the current date or
- CTRL+SHIFT+: to enter the current time
The Solution.
Now, the solution most of my questions refer to is that of entering data into one cell in excel, and a timestamp appearing in another. For example a login sheet, or date entry sheet, login the date and time users entered data. So, there is a way to do this, using formulas but with a little Excel trickery or magic and an understanding of Circular References. Let’s get started.
Circular References Explained.
You may have come across them before, but if not, here’s a quick rundown circular references. A circular reference refers back to itself either directly or indirectly. Here is an example.
If I type =A4+1 in cell A4, I directly refer to the cell containing the formula. The consequence would be a warning from Excel if you did not create this circular reference intentionally. This example of an is intentional, and the result of the formula is zero.
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. So the result is a regular formula.
If I go back to cell A1 and enter the formula =A2+3, this will create a circular formula. I base the formula in A2 on A1, and I am writing the formula in A1. Essentially, the formula is referring to itself. See how this works?.
You can see above that Excel indicates the circular reference with a blue vertical line after selecting OK to its warning. So, let’s take this a step further.
Excel Iteration Settings.
There is a setting in called ‘iterations’ in Excel. This setting controls how many times a formula recalculates. Without this setting, calculations would go into an infinite loop and eventually stall. By default, this setting is switched off in Excel.
So, this is the reason when you type a circular reference, Excel throws you a warning. It stops and doesn’t calculate the formula as it could run infinitely. Simply follow the steps below to change the iteration settings to control how Excel formulas calculate. Take a look for yourself.
- Excel Options.
- Formulas.
- Calculation Pettlings
- Select enable iterative calculation
- Change the default setting to 1. Recalculation will happen once before it stops.
Consequently, in our case, this will result in cell reference A1= 5
So, to insert a timestamp in Excel, we can exploit this circular referencing and iteration to our full advantage. We base the timestamp process on the above theory of iteration.
Type the following formula into cell A1
=IF(B1<>””,IF(A1=””,NOW(),A1),””)
Then type something into B1. The data and time is stamped into the cell. Drag the formula in Column A down to allow time to stamp all the way down the column.
How The Excel Timestamp Works.
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. Excel inserts the timestamp.
So, why not go ahead and give this a go. Let me know if you have any other way of creating a timestamp in Excel?
