When you change any value in a cell, some functions automatically generate new update. One of these functions is NOW().

I currently have =SECOND(NOW()) in A2. I also have a checkbox made with data validation in cell A1. Every time I toggle this checkbox on or off, =NOW() automatically updates to display the current seconds.

However, I want it to keep updating for a set amount of time, defined by cell B1. For example, if B1 contains 5, then when I toggle the checkbox in cell A1, the =NOW() function will constantly update for 5 seconds.

If I were to toggle the checkbox right as the minutes on my clock changed, then I would see A2 read: 0, then a second later 1, then another second later 2, then 3, and then 4, at which point it would stop updating until I toggled the checkbox again.

I've tried a variety of potential solutions to this problem, none of which seem to work. I even tried creating extensive lag through some query functions to cause the cell to delay approximately a second before updating again, however this simply created lag for 5 seconds at which point it displayed a 4, never showing the intermediate 1, 2, or 3. I suspect that this cannot be done with cell formulas, so my question is if I can write a script which will do this for me.

Note: This doesn't necessarily need to be returning the current seconds. All I am trying to achieve is a counter which increments approximately once every second (could be faster as long as steady).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.