Finding Gaps between Time Punches

L

lharnage7331

New Member
Joined
Apr 21, 2021
Messages
6
Office Version
  1. 2016
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
What is the general format of the data...something like columns containing
Name EmployeeID Date Start Stop

What format are the times in?
What are the store hours?
Are all store hours (supposed to be) the same?
Are there any shifts that cross from one day to the next, like 8 PM to 4 AM?

When you say, "Obviously I can see there's an hour window without a punch.", I don't see why this is obvious. Where is the hour window without a punch?
Is your profile up to date regarding your Excel version (Excel 2016)?
 
Last edited:
Upvote 0
Those are some good questions. This is how the punch data comes in.
Each store has their own set of hours.
No shifts overlap from day to day. (Typical hours are 9am/10am to 7pm/8pm with some outliers)
My example with "obviously" was typed incorrectly. I meant if we have 1 store with 1 employee
Store Hours : 9am - 8pm
Employee 1 Clocks 9am - 2 PM
Employee 2 Clocks 9am - 3 PM
No one clocked in from 201PM to 259PM
Excel is fully up to date.

1651857833951.png
 
Upvote 0
Thank you. I'm assuming then that "Punch out" details would be in the same column as the "Punch in" details that you show...so you would have to search by Employee ID and date to obtain a list of all punches for that employee on any given day...and then sort those to reconstruct when they punched in/out to establish their presence on duty? Is that correct?

Does an employee punch in/out only once per day, or are there instances where someone might punch in/out multiple times in a day?

What is the scale of this analysis?...How many days are you thinking about looking at. With 2000 stores, 7500 employees, and x number of days, that's quite a bit of data to organize.

Are the store hours actually flexible, even for a specific store, or does a specific store have firm open/close times. Do you have a cross-reference list of open/close times for each store?

My question about Excel was referring to your account details shown in your profile. I wanted to know if those details are correct--they show that you are using Excel 2016. I ask because Excel 365 offers some improved functionality for filtering, sorting data, but Excel 2016 does not have those functions.
 
Upvote 0
You show an example of the punch data...is it one long continuous list with no other headers, or are there discontinuities in the columns...perhaps blank rows, extra headers, page numbers, etc.?

How do you handle the blank "ID" entries? How would you know whom to assign to those entries?

Sorry...I ask a lot of questions, but I'd like to understand the details before thinking through an approach.
 
Upvote 0
Do the punch times represent the local regional time for that store, or are they relative to some fixed region? If the latter, what is the fixed region? For example, suppose the fixed region is Eastern and someone punches in in Colorado at 8 AM local time (CO)...is the time recorded as 8 AM (local Mountain time) or 10 AM (Eastern time)?
 
Upvote 0
My issue is just trying to figure out how to even start it.


Punch Type does not matter as long as there are consecutive punches
I have both excel types 365 on pc and 2016 on laptop
Store Hours are fixed
Timezone issues can be resolved later on.
It is one long continuous list
There is a column hidden with name in which can reference for iferror but due to privacy i hid the names

As far as scale of the data - run it daily and save the results seperately for a weekly/monthly report. (X amount of minutes on X day where no one was clocked in at Store X)
 
Upvote 0
Thanks. I have an idea for doing this. Are the "punch out" data entries actually labeled as "Punch Out" in the Punch Type column? Are there any other types of entries that appear in that column?

Is the fundamental question then whether there is at least one person punched in during the store's established open time? And if not, then you would like to know to about a minute resolution how long the store had no staff punched in? And you don't care so much about the level of staffing in the store (whether 1, 2, 3, etc. are on duty)...as long as it is at least 1?
 
Upvote 0
If you set up a workbook with a sheet called Stores that resembles this:
MrExcel_20220507_lharnage.xlsx
ABC
1Business Hours for Stores
2
3Store NameOpenClose
4A9:00 AM8:00 PM
5B8:30 AM7:30 PM
6C8:45 AM7:15 PM
Stores

...and another sheet called RawData that resembles this:
MrExcel_20220507_lharnage.xlsx
ABCDEFGHI
1Time Clock Data Dates in Data Set
25/5/2022
3Employee NameEmployee IDStore NamePunch TypePunch TimePunch Date5/4/2022
4name 1ID1APunch In9:19:00 AM5/5/20225/3/2022
5name 2ID2APunch In11:15:00 AM5/5/2022
6name 3ID3APunch In4:03:22 PM5/5/2022
7name 1ID1APunch In9:00:00 AM5/4/2022
8name 2ID2APunch In9:00:00 AM5/4/2022
9name 3ID3APunch In9:00:00 AM5/4/2022
10name 1ID1APunch In9:00:00 AM5/3/2022
11name 2ID2APunch In9:00:00 AM5/3/2022
12name 3ID3APunch In9:00:00 AM5/3/2022
13name 4ID4BPunch In1:00:00 PM5/5/2022
14name 5ID5BPunch In9:00:00 AM5/5/2022
15name 6ID6BPunch In9:00:00 AM5/5/2022
16name 4ID4BPunch In9:00:00 AM5/4/2022
17name 5ID5BPunch In9:00:00 AM5/4/2022
18name 6ID6BPunch In2:00:00 PM5/4/2022
19name 4ID4BPunch In2:00:00 PM5/3/2022
20name 5ID5BPunch In2:00:00 PM5/3/2022
21name 6ID6BPunch In2:00:00 PM5/3/2022
22name 1ID1APunch Out11:00:00 AM5/5/2022
23name 2ID2APunch Out3:00:00 PM5/5/2022
24name 3ID3APunch Out6:00:00 PM5/5/2022
25name 1ID1APunch Out4:00:00 PM5/4/2022
26name 2ID2APunch Out4:00:00 PM5/4/2022
27name 3ID3APunch Out4:00:00 PM5/4/2022
28name 1ID1APunch Out4:00:00 PM5/3/2022
29name 2ID2APunch Out4:00:00 PM5/3/2022
30name 3ID3APunch Out4:00:00 PM5/3/2022
31name 4ID4BPunch Out2:00:00 PM5/5/2022
32name 5ID5BPunch Out11:00:00 AM5/5/2022
33name 6ID6BPunch Out11:00:00 AM5/5/2022
34name 4ID4BPunch Out11:00:00 AM5/4/2022
35name 5ID5BPunch Out11:00:00 AM5/4/2022
36name 6ID6BPunch Out4:00:00 PM5/4/2022
37name 4ID4BPunch Out4:00:00 PM5/3/2022
38name 5ID5BPunch Out4:00:00 PM5/3/2022
39name 6ID6BPunch Out4:00:00 PM5/3/2022
40name 7ID7CPunch In11:30:00 AM5/5/2022
41name 8ID8CPunch In10:30:00 AM5/5/2022
42name 9ID9CPunch In1:00:00 PM5/5/2022
43name 10ID10CPunch In4:00:00 PM5/5/2022
44name 7ID7CPunch Out12:00:00 PM5/5/2022
45name 8ID8CPunch Out12:05:00 PM5/5/2022
46name 9ID9CPunch Out2:00:00 PM5/5/2022
47name 10ID10CPunch Out4:30:00 PM5/5/2022
RawData
Cell Formulas
RangeFormula
I2:I4I2=SORT(FILTER(UNIQUE(F4:F1000),UNIQUE(F4:F1000)<>""),,-1)
Dynamic array formulas.

Then one approach to perform the analysis you described is shown here on the Analysis sheet. This sheet pulls daily data over (using a date selection cell), builds a list of unique list of stores in that filtered data set, and then uses dynamic array formulas to examine each store's punch clock data to determine when no "punched in" staff were present in the store. The results shown break the work day into three blocks: 1) no staff between official store open time and the first punch in (opened late), 2) no staff between the first punch in of the day and the last punch out of the day (everyone disappeared sometime during the middle of the work day), and 3) no staff between the last punch out and the official store close time (closed early). This approach assumes you have some easy way to distinguish between a "punch in" entry and a "punch out" entry in the data. I never received an answer to that question, but it does matter, otherwise creating the necessary arrays will be even more complicated. Each of the formulas will need to be lightly edited so that they specify ranges that match your much larger data set. For the LET functions, the first few arguments of the formulas are the only parts that require such attention.
MrExcel_20220507_lharnage.xlsx
ABCDEFGHIJKLMN
1Analysis of Daily Time Clock Data by Store for Instances of No Staffing
2Date
3Filtered Daily Data5/5/2022Time Unstaffed (h:mm:ss unless noted)
4Employee IDStore NamePunch TypePunch TimePunch DateStoreTotal Time with No StaffBetween First & Last Time Clock PunchesOpened LateClosed EarlyBusiness Hours OpenBusiness Hours CloseTimes Ranges with No Staff (h:mm)
5ID1APunch In9:19:00 AM5/5/2022A3:37:221:18:220:19:002:00:009:00:0020:00:009:00-9:19, 11:00-11:15, 15:00-16:03, 18:00-20:00
6ID2APunch In11:15:00 AM5/5/2022B8:00:002:00:000:30:005:30:008:30:0019:30:008:30-9:00, 11:00-13:00, 14:00-19:30
7ID3APunch In4:03:22 PM5/5/2022C7:25:002:55:001:45:002:45:008:45:0019:15:008:45-10:30, 12:05-13:00, 14:00-16:00, 16:30-19:15
8ID4BPunch In1:00:00 PM5/5/2022
9ID5BPunch In9:00:00 AM5/5/2022
10ID6BPunch In9:00:00 AM5/5/2022
11ID1APunch Out11:00:00 AM5/5/2022
12ID2APunch Out3:00:00 PM5/5/2022
13ID3APunch Out6:00:00 PM5/5/2022
14ID4BPunch Out2:00:00 PM5/5/2022
15ID5BPunch Out11:00:00 AM5/5/2022
16ID6BPunch Out11:00:00 AM5/5/2022
17ID7CPunch In11:30:00 AM5/5/2022
18ID8CPunch In10:30:00 AM5/5/2022
19ID9CPunch In1:00:00 PM5/5/2022
20ID10CPunch In4:00:00 PM5/5/2022
21ID7CPunch Out12:00:00 PM5/5/2022
22ID8CPunch Out12:05:00 PM5/5/2022
23ID9CPunch Out2:00:00 PM5/5/2022
24ID10CPunch Out4:30:00 PM5/5/2022
Analysis
Cell Formulas
RangeFormula
A5:E24A5=FILTER(RawData!B4:F50,RawData!F4:F50=$G$3)
G5:G7G5=UNIQUE(FILTER(B5:B51,B5:B51<>""))
H5:H7H5=SUM(I5:K5)
I5:I7I5=LET( store, G5, dailypunchdata, $C$5:$D$51, dailystore, $B$5:$B$51, sdata, SORT(FILTER(dailypunchdata,dailystore=store),2), pval, IF(INDEX(sdata,,1)="Punch In",1,IF(INDEX(sdata,,1)="Punch Out",-1,"")), csum, MMULT(TRANSPOSE((SEQUENCE(ROWS(pval))<=TRANSPOSE(SEQUENCE(ROWS(pval))))*pval),SEQUENCE(ROWS(pval),,,0)), idxsd, SEQUENCE(ROWS(sdata),,1), idx1sd, SEQUENCE(ROWS(sdata),,2), tsdata, INDEX(INDEX(sdata,,2),idxsd), t1sdata, INDEX(INDEX(sdata,,2),idx1sd), mtgaps, FILTER(t1sdata-tsdata,(csum=0)*(SEQUENCE(ROWS(csum))<>ROWS(csum)),0), IFERROR(SUM(mtgaps),0))
J5:J7J5=LET( store, $G5, dailypunchdata, $C$5:$D$51, dailystore, $B$5:$B$51, storedata, Stores!$A$4:$C$50, sdata, SORT(FILTER(dailypunchdata,dailystore=store),2), open, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,2)), close, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,3)), tfirstp, MIN(INDEX(sdata,,2)), tlastp, MAX(INDEX(sdata,,2)), open2firstp, MAX(tfirstp-open,0), lastp2close, MAX(close-tlastp,0), utbegin, IF(open2firstp=0,"",TEXTJOIN("-",TRUE,TEXT(open,"h:mm"),TEXT(tfirstp,"h:mm"))), utend, IF(lastp2close=0,"",TEXTJOIN("-",TRUE,TEXT(tlastp,"h:mm"),TEXT(close,"h:mm"))), open2firstp)
K5:K7K5=LET( store, $G5, dailypunchdata, $C$5:$D$51, dailystore, $B$5:$B$51, storedata, Stores!$A$4:$C$50, sdata, SORT(FILTER(dailypunchdata,dailystore=store),2), open, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,2)), close, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,3)), tfirstp, MIN(INDEX(sdata,,2)), tlastp, MAX(INDEX(sdata,,2)), open2firstp, MAX(tfirstp-open,0), lastp2close, MAX(close-tlastp,0), utbegin, IF(open2firstp=0,"",TEXTJOIN("-",TRUE,TEXT(open,"h:mm"),TEXT(tfirstp,"h:mm"))), utend, IF(lastp2close=0,"",TEXTJOIN("-",TRUE,TEXT(tlastp,"h:mm"),TEXT(close,"h:mm"))), lastp2close)
L5:L7L5=XLOOKUP($G5,Stores!$A$4:$A$50,Stores!$B$4:$B$50)
M5:M7M5=XLOOKUP($G5,Stores!$A$4:$A$50,Stores!$C$4:$C$50)
N5:N7N5=LET( store, $G5, dailypunchdata, $C$5:$D$51, dailystore, $B$5:$B$51, storedata, Stores!$A$4:$C$50, sdata, SORT(FILTER(dailypunchdata,dailystore=store),2), pval, IF(INDEX(sdata,,1)="Punch In",1,IF(INDEX(sdata,,1)="Punch Out",-1,"")), csum, MMULT(TRANSPOSE((SEQUENCE(ROWS(pval))<=TRANSPOSE(SEQUENCE(ROWS(pval))))*pval),SEQUENCE(ROWS(pval),,,0)), idxsd, SEQUENCE(ROWS(sdata),,1), idx1sd, SEQUENCE(ROWS(sdata),,2), tsdata, INDEX(INDEX(sdata,,2),idxsd), t1sdata, INDEX(INDEX(sdata,,2),idx1sd), utbmid, LET(rowindex, SEQUENCE(ROWS(csum)), colindex, SEQUENCE(1,2), tgaps, IF(INDEX(csum,rowindex)=0, IF(rowindex<ROWS(csum), IF(colindex=1, INDEX(tsdata,rowindex), INDEX(t1sdata,rowindex)), ""), ""), FILTER(tgaps,INDEX(tgaps,,1)<>"","")), utmid, TEXTJOIN({"-",", "},TRUE,TEXT(utbmid,"h:mm")), open, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,2)), close, XLOOKUP(store,INDEX(storedata,,1),INDEX(storedata,,3)), tfirstp, MIN(INDEX(sdata,,2)), tlastp, MAX(INDEX(sdata,,2)), open2firstp, MAX(tfirstp-open,0), lastp2close, MAX(close-tlastp,0), utbegin, IF(open2firstp=0,"",TEXTJOIN("-",TRUE,TEXT(open,"h:mm"),TEXT(tfirstp,"h:mm"))), utend, IF(lastp2close=0,"",TEXTJOIN("-",TRUE,TEXT(tlastp,"h:mm"),TEXT(close,"h:mm"))), IFERROR(TEXTJOIN(", ",TRUE,utbegin,utmid,utend),""))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
G3List=RawData!I2#
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

S
  • Question
Counting the number of employees working between two times.
Replies
2
Views
503
stryped
S
M
  • Solved
Excel formula for overnight hours within a specific time period
Replies
3
Views
3K
Toadstool
Toadstool
AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
288
AOB
AOB
J
  • Locked
  • Question
Still need help on a daily summary sheet
Replies
1
Views
324
Fluff
Fluff
I
  • Solved
Using InStr to Find Matches Between Columns
Replies
1
Views
566
Farscape2020
F

Forum statistics

Threads
1,215,454
Messages
6,124,933
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top