Script Creation: Identifying gaps in attendance

Hi all! I’m an absolute scripting begginer, can I have any support with this which would usually be pretty easy in a spreadsheet but is more challenging in a database.

I need to automatically calculate the ‘deductions’ column which is some thing like

Filter of {Client A} AND {Workshop ID}:
IF(Record No. = 1, 0,
IF({Time In 2} <= {Time Out 1} , 0,
{Time In 2} - {Time Out 1} ))

This assumes the fields are appropriately sorted, where base is ‘Business support admin’, Table is ‘Zoom Calculator’ and fields are ‘Workshop ID’,‘Client Name’,‘Time in’,‘Time out’

Client A records from Workshop ID:
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

Any help would be greatly appreciated

Hi, @Megan_Snape,

The formula references fields named “Time In 2” and “Time Out 1”, but the example records have fields named “Time in” and “Time out”. Are there multiple “Time in” and “Time out” values? Or is “Time In 2” the same as “Time in” (and “Time Out 1” the same as “Time out”)?

The latter seems likely except that the example records conflict with that interpretation of the formula. All of the records have a “Time in” value that precedes the “Time out” value, so (again, assuming “Time Out 1” is the same as “Time out”) all of the “Deductions” values ought to be zero.

1 Like

@Megan_Snape Checking in to see if you were able to figure this out. @Mike_Pennisi asked for some further clarification, but we haven’t seen a response.