Formula for Comparing Timecards

We have physical punch cards (time cards) that we are trying to review. I set up a base with a row for each employee’s card. Each card is for a pay period and the employee has multiple days worked per pay period. The base (redacting employee names and pictures of the punch cards) looks like the following:

I want to find which employees worked together. I’d like to do this by choosing a particular employee and seeing who that person worked with and when they worked together. I would also like to see who worked together by choosing a particular day.

I think this would have been easier had I built the base with each shift as a row, rather than each pay period. Nevertheless, is it possible to extract this information with a formula, lookup field, or rollup field?

I feel like this might have been a job for an if-then formula along with index-match in excel, but I am new to Airtable and am not sure if a formula is where I should start.

I am very grateful for your suggestions.

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.

1 Like

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);
}

1 Like

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.