Can I have a running clock time in a Google spreadsheet?

September 12, 2019 1:57 PM

I'm planning an event in a Google spreadsheet. I've set it up so the durations column always totals- that way I can ensure that the whole event is 120 minutes long, even as individual segment lengths change. Question: I also want a column where I can input a clock start time of, say, 3:45pm, and have the CLOCK start time of each segment automatically correct itself as I adjust the segment lengths. Is this possible?
posted by nouvelle-personne to Computers & Internet (4 answers total)

It seems that if you add a number (e.g.: 1.5) to a time (e.g.: 03:45) in Google Sheets, it treats the number as a number of days. So if the values in your durations colmns is in hours, just divide by 24; if it's in minutes, divide by (24*60).
posted by mhum at 2:48 PM on September 12, 2019

Google Sheets has two different cell formats that're relevant: Duration and Time. Time cells have an AM/PM component (or it'll be 24h time, depending on your account localization). Format -> Number -> (Duration or Time)

You want to use Duration for your Durations, of course, but you can add and subtract those from Times. In both cases, you need to enter h:m:s -- you can ignore leading zeros (2:5:5 = 2:05:05), but if you don't enter all 3, it assumes h:m. When entering a Time, 15:45:0 will get you 3:45 PM, or you can type 3:45:0 PM

So you could create a (Time-formatted) entry box where you put in the Start Time, and use Time-formatted cells when you're doing math which outputs a Time, whereas if you wanted to subtract one Time from another, or a fixed amount of minutes from another (in a Duration field), you want to do that in a Duration-formatted field.

If you find yourself with a Time output that's really early in the morning, chances are you formatted a Duration as a Time by mistake. In other words, if you subtract one Time from another and get 12:10:05 AM, that means that you formatted for Time what should've been a Duration (time-difference) of 10m5s, 00:10:05.

It looks like I can paste my test spreadsheet in here without ruining anything too badly-- added some pipes and dashes for sectioning. The left column is formatted for durations while the right column is formatted for Time. I hope it'll be reasonably apparent as to how I got what I got; adding durations to times, starting with adding the first duration to the start time.

Durations | Times
Start time -> | 3:45:00 PM
---------------------------------
Durations | Segment End
0:23:01 | 4:08:01 PM
0:05:06 | 4:13:07 PM
0:03:00 | 4:16:07 PM
0:23:01 | 4:39:08 PM
0:05:06 | 4:44:14 PM
0:03:00 | 4:47:14 PM
---------------------------------
Total Time Ending time
1:02:14 | 4:47:14 PM
posted by Sunburnt at 3:23 PM on September 12, 2019 [2 favorites]

There is a "time" function that accepts hours, minutes and seconds. You can use this to easily add minutes to a time.
Here's an example: https://imgur.com/a/Sx6ybRJ
posted by many more sunsets at 4:23 PM on September 12, 2019 [1 favorite]

Check your messages - I sent you a sheet that I think does (most of) what you want (using the date math that mms suggests.

(I make these all the time. My job involves a lot of this sort of scheudling)
posted by ManInSuit at 6:50 PM on September 12, 2019 [1 favorite]