TIme Clock record reformatting to ???

S

Sleeplol

Board Regular
Joined
Apr 10, 2019
Messages
194
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello All,
I'd like some help formatting this (Tbl-A)(Loaded via Power Query)
PQdata.png


into this (Tbl-B)
FormattedData.png


What I used to populate Tbl-B Columns (All the same Index/Match formula logic):
Employee Id - IFERROR(INDEX($B$2:$B$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time In - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$1, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")
Time Out - IFERROR(INDEX($A$2:$A$1000, SMALL(IF(ISNUMBER(SEARCH($L$2, $C$2:$C$1000)), MATCH(ROW($C$2:$C$1000), ROW($C$2:$C$1000))), ROWS($A$2:A2))),"")

Problems:
  • If someone fails to clock in or out, the entire column is thrown off
  • The In and out times are not linked to the Employee ID so they are essentially listed in order of appearance. The red on Tbl-B indicates the start of error (Matching Tbl-A)
    BothPics.png
Also, the new shifts are going to be 12 hour days and 12 hour nights, and there will ALWAYS be times that folks forget to clock in or out. So is there a way to "skip" a clock out if over 15 hours from clock in time.

Please let me know the best way of going about this whether in Power Query or excel.
Many thanks for any help with this
 
Sort by date Sort by votes
That's so strange.
Per your advice I tested both posts 5 and 8 in a different sheet, and they both work fine...
But
When I copy the values from the original A:D range into the 5 and 8 copies, it gives me the same repeating result, there is something about row 11 (maybe) that causes issues.

Well, your formulas clearly works, at least I know this is entirely on my end. I wish I could just upload my WB.

Hey, thanks for all of your help Jason
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If I paste in Row 26 on the data then it works fine. Something about that row.
I'm on my work system so I can't up or download. If I can't figure it out then I'll send over something tonight if you're willing to still look into it. I'll post back if I manage it on my own.
 
Upvote 0
It could be floating point precision causing inaccuracy with the decimal (time) part of the cell.

I have seen one previous case where that was the cause of discrepancies in time based formulas where 2 people were getting different results from the exact same data and formulas.
Sleeplol said:
If I paste in Row 26 on the data then it works fine.
Click to expand...
Try formatting the cell as general and see what value is in the formula bar (when the formulas are showing the wrong results), then paste it as you have done to see if it changes slightly.
 
Upvote 0
jasonb75 said:
It could be floating point precision causing inaccuracy with the decimal (time) part of the cell.

I have seen one previous case where that was the cause of discrepancies in time based formulas where 2 people were getting different results from the exact same data and formulas.

Try formatting the cell as general and see what value is in the formula bar (when the formulas are showing the wrong results), then paste it as you have done to see if it changes slightly.
Click to expand...
Hey James,
Would you take a look at this file. I did your test and I didn't see a difference in the formula bar. *scratching head*
 
Upvote 0
If you upload it to dropbox (or similar) and post a link then I'll take a look at it for you.
 
Upvote 0
May have to refresh query, I don't believe I set it to "on-open"
 
Upvote 0
This is the result I see when I open your file.

TimeSheet(Temps).xlsm
ABCDEFGHIJ
1Timezone UTCEmployee ID:No LabelIPEmployeeEmployee IDTIME INTIME OUT
28/6/20 7:02 AM8907Clock IN96.65.210.85Joseph Dutch89078/6/20 7:02 AM8/6/20 3:05 PM
38/6/20 9:21 AM6422Clock IN96.65.210.85Brent Wilette64228/6/20 9:21 AM8/6/20 8:07 PM
48/6/20 3:05 PM8907Clock OUT96.65.210.85Joseph Dutch89078/7/20 7:05 AM8/7/20 2:49 PM
58/6/20 8:07 PM6422Clock OUT96.65.210.85Brent Wilette64228/7/20 2:49 PM8/7/20 2:59 PM
68/7/20 7:05 AM8907Clock IN96.65.210.85Joseph Dutch89078/10/20 7:09 AM8/10/20 3:08 PM
78/7/20 2:49 PM6422Clock IN96.65.210.85Brent Wilette64228/10/20 8:24 AM8/11/20 7:56 AM
88/7/20 2:49 PM8907Clock OUT96.65.210.85Joseph Dutch89078/11/20 7:04 AM8/11/20 3:09 PM
98/7/20 2:59 PM6422Clock OUT96.65.210.86Brent Wilette64228/11/20 7:56 AM 
108/10/20 7:09 AM8907Clock IN96.65.210.85Joseph Dutch89078/12/20 7:04 AM8/12/20 3:02 PM
118/10/20 8:24 AM6422Clock IN96.65.210.85Joseph Dutch89078/13/20 7:05 AM8/13/20 3:02 PM
128/10/20 3:08 PM8907Clock OUT96.65.210.85Joseph Dutch89078/14/20 7:08 AM8/14/20 3:00 PM
138/11/20 5:08 PM6422Clock OUT96.65.210.86Joseph Dutch89078/17/20 7:06 AM8/17/20 3:11 PM
148/11/20 7:04 AM8907Clock IN96.65.210.85Joseph Dutch89078/18/20 7:02 AM8/18/20 3:03 PM
158/11/20 7:56 AM6422Clock OUT96.65.210.85Joseph Dutch89078/19/20 7:02 AM8/19/20 3:03 PM
168/11/20 7:56 AM6422Clock IN96.65.210.85Joseph Dutch89078/20/20 6:59 AM8/20/20 3:03 PM
178/11/20 3:09 PM8907Clock OUT96.65.210.85Joseph Dutch89078/24/20 7:09 AM8/24/20 3:01 PM
188/12/20 7:04 AM8907Clock IN96.65.210.85Joseph Dutch89078/25/20 7:07 AM8/25/20 3:01 PM
198/12/20 3:02 PM8907Clock OUT96.65.210.85Joseph Dutch89078/26/20 7:07 AM8/26/20 12:55 PM
208/13/20 7:05 AM8907Clock IN96.65.210.85Joseph Dutch89078/27/20 7:04 AM8/27/20 3:00 PM
218/13/20 3:02 PM8907Clock OUT96.65.210.85Joseph Dutch89078/28/20 7:08 AM 
228/14/20 7:08 AM8907Clock IN96.65.210.85Joseph Dutch89078/31/20 7:05 AM8/31/20 3:01 PM
238/14/20 3:00 PM8907Clock OUT96.65.210.85Joseph Dutch89079/1/20 7:08 AM9/1/20 2:59 PM
248/17/20 7:06 AM8907Clock IN96.65.210.85Joseph Dutch89079/2/20 7:05 AM9/2/20 3:00 PM
258/17/20 3:11 PM8907Clock OUT96.65.210.85Joseph Dutch89079/3/20 7:03 AM9/3/20 3:02 PM
268/18/20 7:02 AM8907Clock IN96.65.210.85Joseph Dutch89079/4/20 7:09 AM9/4/20 3:03 PM
278/18/20 3:03 PM8907Clock OUT96.65.210.85Joseph Dutch89079/7/20 7:05 AM9/7/20 3:01 PM
288/19/20 7:02 AM8907Clock IN96.65.210.85Joseph Dutch89079/8/20 7:04 AM9/8/20 3:11 PM
298/19/20 3:03 PM8907Clock OUT96.65.210.85Joseph Dutch89079/9/20 7:03 AM9/9/20 3:06 PM
308/20/20 6:59 AM8907Clock IN96.65.210.85Joseph Dutch89079/10/20 7:07 AM9/10/20 3:01 PM
318/20/20 3:03 PM8907Clock OUT96.65.210.85Joseph Dutch89079/11/20 7:09 AM9/11/20 3:01 PM
328/21/20 3:03 PM8907Clock OUT96.65.210.85Joseph Dutch89079/14/20 7:05 AM9/14/20 3:01 PM
338/24/20 7:09 AM8907Clock IN96.65.210.85Joseph Dutch89079/15/20 7:09 AM 
348/24/20 3:01 PM8907Clock OUT96.65.210.85Calvin Lane88389/15/20 7:30 AM9/15/20 4:02 PM
358/25/20 7:07 AM8907Clock IN96.65.210.85Mick Franklin78409/15/20 7:31 AM9/15/20 4:01 PM
368/25/20 3:01 PM8907Clock OUT96.65.210.85Mick Franklin78409/16/20 6:54 AM 
378/26/20 7:07 AM8907Clock IN96.65.210.85Calvin Lane88389/16/20 6:54 AM 
388/26/20 12:55 PM8907Clock OUT96.65.210.85    
398/27/20 7:04 AM8907Clock IN96.65.210.85    
629/14/20 7:05 AM8907Clock IN96.65.210.85    
639/14/20 3:01 PM8907Clock OUT96.65.210.85    
649/15/20 7:09 AM8907Clock IN96.65.210.85    
659/15/20 7:30 AM8838Clock IN96.65.210.85    
669/15/20 7:31 AM7840Clock IN96.65.210.85    
679/15/20 3:01 PM8907Clock OUT96.65.210.85    
689/15/20 4:01 PM7840Clock OUT96.65.210.85    
699/15/20 4:02 PM8838Clock OUT96.65.210.85    
709/16/20 6:54 AM7840Clock IN96.65.210.85    
719/16/20 6:54 AM8838Clock IN96.65.210.85    
DATA
Cell Formulas
RangeFormula
G2:G39,G62:G71G2=IFERROR(VLOOKUP([@[Employee ID]],Temps!E:F,2,FALSE),"")
H2:H39,H62:H71H2=IF(I2="","",IFERROR(VLOOKUP(I2,$A$2:$B$10005,2,0),""))
I2:I39,I62:I71I2=IF(ROWS(I$2:I2)>COUNTIF($C$2:$C$10005,$L$1),"",MINIFS($A$2:$A$10005,$A$2:$A$10005,">"&N(I1),$C$2:$C$10005,$L$1))
J2:J39,J62:J71J2=IF(ROWS(J$2:J2)>COUNTIF($C$2:$C$10005,$L$1),"",IFERROR(1/(1/MINIFS($A$2:$A$10005,$A$2:$A$10005,">"&I2,$B$2:$B$10005,H2,$C$2:$C$10005,$L$2,$A$2:$A$10005,"<"&MINIFS($I$2:$I$10000,$H$2:$H$10000,H2,$I$2:$I$10000,">"&I2))),""))
 
Upvote 0
Hello Fluff,
Please refresh the query.
Then column J repeats at A26
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
modify excel formula
Replies
5
Views
223
Peter_SSs
Peter_SSs
B
  • Solved
Array Returning "0" value
Replies
5
Views
189
Fluff
Fluff
J
  • Question
Formula to move information to calendar
Replies
0
Views
81
jc352
J
J
  • Question
Formula to find date and return column number
Replies
2
Views
158
jc352
J
G
  • Question
Moving the formula to the next column
Replies
7
Views
114
Geo Jul
G

Forum statistics

Threads
1,214,839
Messages
6,121,887
Members
449,057
Latest member
Moo4247

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