# Identify gaps in time frames

Topic Labels: Formulas
Solved
1266 6
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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?

1 Solution

Accepted Solutions
14 - Jupiter

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.

6 Replies 6
14 - Jupiter

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.

Airtable Employee

@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.

Airtable Employee

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.

5 - Automation Enthusiast

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!

5 - Automation Enthusiast

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

5 - Automation Enthusiast

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.