Google-sheets – Values are being updated lated using formula

formulasgoogle sheets

I have a sheet on which 5 to 7 users are working concurrently to track their project time. We are trying to get current time when the user write Yes in the specified column or click the checkbox. However, Lots of the time, there is a difference between the time of updates of both the columns.

Col j – it is manual entry by the user
Planned and Actual are based on formula
Actual value – =if(H748,H748,if(J748<>"",$A$1,""))
In $A$1 = now()

Below is the snapshot for the reference

Best Answer

The differences occurs because Google Sheest formula recalculation is done localy and NOW() is a volatile function. This means that each time the a user makes a change NOW() will return a different value so it's not a good idea to use it to set a timestamp, instead use Google Apps Script. We have a lo of questions about using Google Apps Script to set timestamps when a cell is edited but this might introduce a complexity that might not be worthy to have compared to ask users to add the time by themselves.

Related Topic