Consolidating 2 data sets into a table to show a difference in clock in/clock out times

[Buzz1126]

Hey guys,

I'm currently helping a friend out with a problem she is stuck on. I got stuck as well. I attached a representative sample of the data along with a proposed solution.

Her goal is to consolidate the data so that we can see the difference in their clock in/clock out times on the two different systems. An ideal solution is attached and shown below.

We pull in data from Truck Drivers from two different systems, XRS and Kronos. The data she is worried about are: name, date, clock in, clock out.

Currently there are 2 different tabs, XRS and Kronos representing the different sources. Not all names depicted in Kronos are in XRS, however all names in XRS are in Kronos. The third tab is the ideal solution page.

Please let me know if there are any questions.

Thank you guys in advance.

Capture.PNG

[Buzz1126]

Is this even possible? I'm pretty excel savy so even pointing me in the right direction might help. Thank you.

[Bo_Ry]

Please try at
C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

E2
=IFERROR(ABS(D2-C2),"")

[Buzz1126]

Yes! Thank you! i'll apply it to my original sheet just to make sure then i'll mark as solved.

[Buzz1126]

Please try at
C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

E2
=IFERROR(ABS(D2-C2),"")

The only problem I'm running into is on the Kronos Out. It's pulling the first punch out for lunch not the punch out for the end of the day.

[Bo_Ry]

Please check the attached files from post#3

F2
=LOOKUP(1,0/(Kronos!$A$9:$A$68=A2)/(Kronos!$B$9:$B$68=TEXT(B2,"m/dd/yyyy")),REPLACE(Kronos!$I$9:$I$68,LEN(Kronos!$I$9:$I$68)-1,0," "))

[Buzz1126]

Ahh my bad I just saw the formulas. Thank you