Automatic timestamp with no script in Google Sheets and no VBA in excel…
One of the most asked questions in excel and Google Sheets forums is how to present the date and time next to the cell who was entered the value in.
Which means how do we follow the exact time in which the data was entered in a specific cell.
You can use this application in numerous examples, such as entering data of a study when we want to know the exact time and date this data was entered, or when many people are working on the same file and want to update everyone on the exact date and time a deal was made with a client.
The way to perform this magic is with a circular formula in which we refer the formula to the same cell that it is written in.
There are two options:
1. a timestamp it updates with every change that we did on the cell next to it
2. at timestamp that doesn't change in a cell even if we delete it the value next to it
Option 1:
The example:
We have a table with our clients’ data, the table follows the calls we make with our clients and there are several people who works on the same file simultaneously, we want to follow the exact time and date of the call because we need to make a follow-up call three days later.
After we logged the call, we mistyped the name of the client and only the day after we wanted to fix the name but we don't want the timestamp to change unless we completely delete the data in the cell.
the table is:
The formulas entered:
In cell B2 which we want the time stamp for the data entered in cell A2 we will enter the circular formula of the timestamp-
=IF(A2<>"",IF(B2<>"",B2,NOW()),"")
The formula checks in cell A2 and in cell B2 if there is a value, if there is a value, the value in B2 will be returned, if in A2 there is a value and in B2 there is no value the formula will return the timestamp, otherwise the cell will remain empty. as long as in A2 there is a value the timestamp will not change.
For the follow up call in cell C2 we will enter the formula:
=B2+3
the formula adds 3 days to the timestamp in cell B2.
Option 2:
In a file which we want to preserve the timestamp no matter what happens to the cell next to it- for example a test that we send to our students in the Academy or a task that we send to our employees.
For example:
Two chess players are playing a far. for the recording their chess moves they are playing on the same Google sheet file, since we need to count the time each player, the time stamp for the beginning of the game and for each play is important, we want the time stamp for each play to be frozen even if we accidentally deleted the cell in which we entered the move.
The Chess players table:
We will enter the formula in cell C2:
=if(if(and(B2="",C2=""),"",if(C2<>"",C2,now()))=0,"",if(and(B2="",C2=""),"",if(C2<>"",C2,now())))
The formula states that as long as both cells B2 and C2 are empty, return an empty cell, but when there is a value in cell B2, we should always return the time stamp that initially was registered in C2 even if the data in B2 was deleted.
We need to take care of two things:
1. To fix the format of the cell with the timestamp so we could see the date and the time together.
2. Important! since the formula is circular which means it reference the calculation to itself, we need to make sure that we will not get an error message.
In excel:
File--->Options--->Formulas--->Enable Iterative Calculation
In google sheets:
File--->Settings---->Calculations---->Iterative calculation--->On
Good Luck!
תגובות