Identify gaps in time frames

[Megan_Snape]

Hi all! Thanks for taking the time to read this, I am migrating from Google Sheets and really need an inventive solution to this problem. I’d love to know your thoughts.

My company run Zoom workshops and we are required to generate registers with time in/out. Zoom gives me a CSV file with this data, but it often enters the same client accross multiple rows. I then need to apply deductions for any period of time where the client was offline during the session.

Time in Time out Deductions
1 ␣ 09:28 ␣ 09:37 ␣ 00:00
2 ␣ 09:34 ␣ 10:16 ␣ 00:00
3 ␣ 09:44 ␣ 10:16 ␣ 00:00
4 ␣ 10:18 ␣ 10:37 ␣ 00:02
5 ␣ 10:19 ␣ 10:37 ␣ 00:00
6 ␣ 10:38 ␣ 10:44 ␣ 00:01
7 ␣ 10:40 ␣ 10:44 ␣ 00:00

I can use a roll up to identify the MAX and MIN times, but cannot work out the deductions. To achieve this in sheets I did {Time out 2} - {Time in 1}.
This had to be applied once the data was appropriately sorted using a macro.

Clearly this does not work in Airtable - any other smart ideas?

[Jeremy_Oglesby · Accepted solution]

You’ll need to use a Script in the Scripting block for this kind of thing. Google Sheets can do it with a macro because each “cell” in a column can reference the data in any other “cell” in the sheet. Airtable can’t do that the same way, because it’s not a spreadsheet with static “cell” locations. What looks like a “cell” in Airtable is actually a data point attached to a record - it goes with the record, wherever that record ends up in the sorted order of a table or view.

The Scripting Block, however, can handle this with JavaScript. You need a little scripting skill, but it shouldn’t be too terribly difficult to work it out.

[Jason]

@Jeremy_Oglesby I may be missing a detail here, but using duration fields (both for the timestamps, and for the rollup field format) plus linking records between tables should work out.

[Jason]

Ah, actually I see the problem with my response above - you’d need separate rollup fields to log multiple in/out timestamps, and then sum up the duration of all of them combined.

[Megan_Snape]

Thanks Jason,

For some reason I also end up with overlap on times from Zoom, so this would end up duplicating minutes - but I did a double take becuase its a very logical suggestion!

[Megan_Snape]

Thanks Jeremy! I’d understood my records will not behave as cells but it is good to know a script will handle this.

[Megan_Snape]

Hi Jeremy, so… I am lacking the ‘little scripting skill’ :grinning_face_with_sweat: Do you have any thoughts up your sleeve? Else I will need to post for further support as I’m up against a deadline. I am a total beginner and haven’t needed to do anything so specific before.