Timeclock report analysis - timecard report captures clock in, clock out, clock in again

Hello,

I am trying to automate a payroll calculation. Our security system outputs an excel report with any desired time period that logs activity of each employees swipe card. We have the following shifts:

Day Shift: 7am - 7:30 pm
Afternoon Shift: 3:30- 1:00am
Night Shift: 7:30pm - 7:00am


My issue is if the report covers a 24hr period it will include each person clocking in, clocking out, and clocking in again to start their next shift. To do the shift duration calculation I need to have only 2 time events.

This report is plugged into another spreadsheet which calculates the time worked between clock in and out for each daily report and collects all of the information into a weekly timesheet summary.

I have added a step to use a countif function to flag duplicate entries but this requires the user to go in an manually remove the duplicates.

I need a method to sort the raw data, eliminate the timestamp from the start of the second shift.

Thanks for any ideas you have.