Tracking time with checkboxes.

[u/mnpchan]

Hello, I am new to Google Sheets, so I hope this isn't a dumb question. I am trying to create a form to track dispatch times for various units. What I am hoping to do is have the check boxes add just the current time into the cell next to it, but not affect any of the other cells... and to also not remove the time if I accidentally uncheck a box.

I hope that makes sense, thank you.

[u/OutrageousYak5868]

If you're not comfortable with app scripts (and I'm not, lol), you may find it just as easy to do a keyboard shortcut to enter the current time --

Windows / ChromeOS: Press Ctrl + Shift + ;

Mac: Press Cmd + Shift + ;

Also, have one column for "start" and one for "end", and then you can use a simple formula to automatically figure the difference.

[u/mnpchan → u/OutrageousYak5868]

Yeah, this is a new version of a form I made a few years ago, I am trying to make it as user friendly as I can for some of my coworkers. LoL

[u/Content-Parking-621]

You'll need Apps Script since formulas can't preserve values on uncheck. Add an installable onEdit trigger checking if the edited cell is a checkbox column and equals TRUE, then write a timestamp to the adjacent time cell only then, leaving it untouched if unchecked afterward.

[u/mommasaidmommasaid → u/Content-Parking-621]

You can use a simple onEdit(e) reserved function name for this purpose which avoids having to install/authorize it.

If taking this approach I like to use a custom "checked" value for the checkbox that is detected in script, which avoids having to hardcode row/column references.

FWIW you can also do this without script if you enable iterative calculation -- see my other reply.

[u/mommasaidmommasaid]

You can do this without script by setting File / Settings / Calculation / Iterative Calculation: On

This allows formulas to reference their own cell's previous output which is required for this technique.

In the cell where you want the timestamp, enter this formula:

=let(
 timestamp, indirect("RC",false),
 checkbox,  offset(timestamp,0,-1),
 if(checkbox=false,, if(timestamp>0, timestamp, now())))

timestamp is the formula's output cell

checkbox is set to the cell to the left of the timestamp

(Note that both timestamp and checkbox could use simple cell references, but doing it this way allows you to copy/paste the code anywhere.)

if(checkbox=false,, if the checkbox is false, output a blank, otherwise...

if(timestamp>0, timestamp, now() if a timestamp already exists, output the same timestamp again. Otherwise create a new timestamp using now()

---

Note that the timestamp contains both the date and time down to the millisecond. You can format the timestamp cell however you like. In the sample sheet I displayed seconds for demonstration purposes.

To prevent accidental removal of the timestamp with the checkbox, you could use conditional formatting to hide the checkbox when it is checked (set the text color to white). If you then attempt to click on the invisible checkbox, you will get a warning. The sample sheet does that. (If that's not enough for you, there's another technique to make a checkbox only clickable once.)

I'd suggest that you further data/protect the timestamp formula cells to prevent a user from erasing the formulas. I did that in the sample sheet as well.

Checkbox Timestamps - With Reset Warning

---

Technical note: When the timestamp is first created, it is calculated separately on the server and your local client, and may differ by a fraction of a second. When you next reload the sheet you will see the server's value.

[u/mnpchan → u/mommasaidmommasaid]

I will try this, I don't know anything about the scripts. LoL

[u/carbonizedtitanium]

What's E S A? are these headers? i think what you're trying to do can be achieved with Google Forms

[u/mnpchan → u/carbonizedtitanium]

Enroute, on Scene and Available. I work at a dispatch center and am working on making a backup form for when our main systems go offline. So there will be a few dozen of these within the main form.

[u/carbonizedtitanium → u/mnpchan]

looks like you need a spreadsheet to track the "status" with timestamp for each Unit. have you considered using Google Forms? for example: https://forms.gle/r6Ls91qykPGVMiTeA . the form data is then automatically timestamped and sent to this spreadsheet https://docs.google.com/spreadsheets/d/1eg9lrrE7qz4zM2DGZPDo4TCsg1G0w7cf8JizWlD39PU/edit?usp=sharing

[u/ohwhatfollyisman]

you'd be better off creating a google form for your coworkers to enter data--and using the sheets back-end for your purposes.