Walking through the logic, if Data is not empty, then there are three possible cases. 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. Data: (still in cell C3), this is where you enter your dataĢ).
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.ġ).
Note that:ġ) 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Ģ) 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)Īnyway, 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. 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.
Januat 7:46 is how to change the timestamp when the data cell is modified. PS: Having questions on formulas, try our excel formula helper tool and learn 50+ forumals in a fun way The above formula works only if you have enabled iterative calculation mode as described above. 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.
iterations per formula execution is 100, which means, every time you run the circular formula, excel calculates it 100 times before presenting the value. In excel 2007, you can go to office button > excel options > formulas > iteration area.Īs you can see the default max. =if(A2 tools > options > calculation tab and check the iteration box. Confused ? Well, let me give you an example. What the heck is a circular formula?Ī circular formula refers to itself (or to another cell which in turn refers to this cell).
But the only issue is, every time you need a time stamp you have to press the 2 keys.Ī better way to get timestamps in excel is to use a special type of formulas, called as circular formulas. Since this places the value, we don’t need to worry even if the date or time changes. Thus we cannot use NOW() as a time stamp.Ī good alternative (although manual) is to use keyboard shortcuts CTRL + or CTRL + : to insert current date and time in the active cell. 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. We all know about the now() formula in excel and how it tells you the current date and time. So how do I generate timestamps in Excel? Once the item is time stamped, it is much more easier to analyze it. Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time).