0

I am trying to make a simple logic on google sheets but there is some error. I want to 3 check boxes and when they are marked current time is saved.

so far i am using these three statements


=if($B$2=TRUE(),NOW(),0)
=if($B$3=TRUE(),NOW(),0)
=if($B$4=TRUE(),NOW(),0)

b2, b3 and b4 are cells addresses for check boxes. The problem i am facing is when i check B2 it gives me the current time. But when i check B3 it gives me current time and also changes the time of B2 similarly when i check b3 it changes the time for all three to the current time.

Why is this happening and what could be the probable solution.

Thankyou in advance

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
fil
  • 3
  • 1

1 Answers1

0

You cannot get a permanent timestamp with a spreadsheet formula, even with a named function or an Apps Script custom function, because formula results refreshed from time to time. When the formula gets recalculated, the original timestamp is lost.

The easiest way to insert the current date in a cell is to press Control + ; or ⌘;. See the keyboard shortcuts help page.

You can also use an onEdit(e) script to create permanent timestamps. Search this forum for [google-apps-script] timestamp to find many examples.

doubleunary
  • 13,842
  • 3
  • 18
  • 51
  • [Lambda(x,x)](https://stackoverflow.com/a/66201717/) does freeze it, when not used as a array formula. – TheMaster Nov 02 '22 at 06:33
  • The stress is on the word _permanent_. When the lambda gets recalculated, which does happen from time to time, the original timestamp is lost. – doubleunary Nov 02 '22 at 07:59
  • `which does happen from time to time,` Do you mean automatically? I haven't seen it so far. What events trigger this? – TheMaster Nov 02 '22 at 08:10
  • 1
    User-triggered refreshes happen e.g. with copy-paste and search and replace in formulas. System-triggered refreshes take place with certain spreadsheet engine updates. A notable global refresh happened during the 2014 "New Sheets" rollout when a myriad formula based timestamps got reseted globally. – doubleunary Nov 02 '22 at 09:48