Finding Gaps between Time Punches

[lharnage7331]

So my boss comes to me today.... wants to find out if we have stores closing without our knowledge.

We have over 2000 stores
Over 7500 employees
They clock in via an APP and all punches are recorded
Say i have a daily set of punches by employee by store

Can someone for the life of me give me a direction on how i would pretty much find time gaps between those punches (Assuming everyone clocked in and out correctly)

Example: Employee clocks 9-2. Another Employee Clocks in 2-8. Obviously I can see there's an hour window without a punch. But when we have stores with 1-7+ employees, 4 punches for each employee across 2000 stores I need an excel way to find these gaps where one person's punch does'nt overlap another person's punch therefore assuming the store had no employees working at that time

 

[lharnage7331]

Oh nevermind - i can copy from the forum. This has to be the best answer on Mr Excel ever

 

[KRice]

lol...contributions here are voluntary. I'll post a link to the file after I clean it up a bit (and as you've noted, you can do a copy/paste directly from the site). I am guessing that you might want to sort or filter the results table? Right now, with the daily store listing being generated by a "spilling" array function (UNIQUE), you won't be able to use any autofilters. And the presence of the daily punch data on the same worksheet may be an issue too, as rows of it would be collapsed if an autofilter were applied to the results table. So I'm cleaning up a version 2.0 that moves the daily punch data to its own worksheet and the analysis/results table is left alone on a worksheet. I'll try to include some instructions for inserting the store listing information and reviewing the formulas to ensure they reference the correct data set/columns.

 

[KRice]

Here is a link to the file, reworked as described in my last post. Review the text boxes on each worksheet for details, insights, and recommendations. Post back if you have any questions or encounter any issues.

MrExcel_20220507_lharnage2.xlsx

Shared with Dropbox

www.dropbox.com