Formula for Comparing Timecards

Hi Wingo, I can’t think of a way to do that I’m afraid, hopefully someone else has an idea

Let me know if you could use help converting each shift into a row and I’ll see what I can do for you

Welcome to the Airtable community!

Yes, it would be easier to analyze the data if you had a two table system and one row per shift. One table for people, and a linked table for shifts worked.

It would then be easier to use formula fields to identify unusual data, such as when the clock-in time is after the clock-out time, or there is a long shift (over 12 hours). Both of these things occur in your data.

No, it is not possible using only formulas/lookups/rollups. This is because your shifts do not have standard hours. If your shifts had standard hours, it would be possible to use a third table of shifts to see who worked the same shifts. But it looks like that won’t work for your data.

You could do a linked table of dates to see who all worked the same dates, but there could be people who worked the same date without overlapping shifts.

It is possible to use scripting to find who had overlapping shifts. This would require knowing or hiring someone with both Airtable knowledge and scripting ability. This is not a beginner script.

Thanks, Adam. Switching the base design to have one shift per row seems like the best option right now. I’d appreciate your suggestion on how to do that with the existing base we have.

Thank you, Kuovonne! Your reply was very helpful. I’m going to switch base design because we don’t have the ability to do advanced scripting.

Hey Wingo, give the following script a shot. You’ll need to install a scripting app, update the table name, employee field names and such, but this should work.

I’ve got it set up in this base so you can see an example of a full base set up + the script

The idea is that you just input the total number of clock in / outs you have, and, for each one of those, the script will create a new record in the new table, and it’ll do so for each employee

Let me know if there are any errors and I’ll fix them up

After it gets converted to shifts, you should be able to get the data you’re looking for pretty easily by converting the employees to a linked row I reckon; let me know if you wanna discuss any of that too

//Fill in your data here
let dataTable = base.getTable([NAME OF THE TABLE THAT CONTAINS YOUR CURRENT DATA])
let tableToUpdate = base.getTable([NAME OF THE TABLE THAT YOU ARE PUTTING THE NEW SHIFT DATA INTO])
let employeeFieldName = [NAME OF THE FIELD THAT CONTAINS THE EMPLOYEE INFORMATION FOR BOTH TABLES]
let numClockIns = [THE HIGHEST NUMBER OF CLOCK INS]
// e.g. if the larget clock in field you have is `CLOCK IN - 1000`, then put the number 1000 here

// Example set up
// let dataTable = base.getTable('Data Table')
// let tableToUpdate = base.getTable('Shifts')
// let employeeFieldName = 'Employee'
// let numClockIns = 2
//=====
let fieldsToGet = [employeeFieldName]
for (let i = 0; i < numClockIns; i++){
    let clockInNumber = i + 1;
    fieldsToGet.push('Clock in - ' + clockInNumber)
    fieldsToGet.push('Clock out - ' + clockInNumber)
}

let query = await dataTable.selectRecordsAsync({
    fields: fieldsToGet
})

let recordsToCreate = new Array

for (let record of query.records){
    for (let i = 0; i < numClockIns; i++){
        let recordToCreate = { fields: {}}
        let clockInNumber = i + 1;
        recordToCreate.fields[employeeFieldName] = record.getCellValueAsString(employeeFieldName)
        recordToCreate.fields['Clock in'] = record.getCellValue('Clock in - ' + clockInNumber)
        recordToCreate.fields['Clock out'] = record.getCellValue('Clock out - ' + clockInNumber)
        recordsToCreate.push(recordToCreate)
    }
}

while (recordsToCreate.length > 0) {
    await tableToUpdate.createRecordsAsync(recordsToCreate.slice(0, 50));
    recordsToCreate = recordsToCreate.slice(50);
}