Aug 02 2020 06:05 PM
sheet1 has a column that list "employee names" and a column for their "punch in" times.
sheet2 has a column that list "employee names" and a "start work" time column. (time when they turn the key on their work truck and we have a system that records the time and we call it "start work" time.
i need the data from sheet 2 copied to sheet1 and formula to show difference in each employees "punch in" time and their "start work" time.
any help would be greatly appreciated.
thanks,
daniel
Aug 02 2020 07:15 PM
@dank1868 , could use a bit more explanation. For example,
Here's one employee's details from Sheet1:
Employee Number | first | last | Name | Status Description | Change Type | 7 | ||
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | CLOCK IN/PRE-TRIP | Manual | 2020-07-29 05:53:49 |
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | IN SERVICE | Manual | 2020-07-29 06:02:56 |
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | OUT OF SERVICE | Manual | 2020-07-29 16:02:10 |
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | CLOCK OUT | Manual | 2020-07-29 16:22:10 |
Here's the same employee's information from Sheet2:
Name | HomePlt | OnDuty | PunchIn | PunchOut | Shift | PPD | DOT |
Contreras, Robert | 715 | YES | 2020-7-29 5:50 | 14.17 | 36.67 | 36.67 | |
2020-7-27 0:50 | 2020-7-27 13:07 | 12.28 | |||||
2020-7-28 5:35 | 2020-7-28 15:48 | 10.22 | |||||
2020-7-29 5:50 | 14.17 |
How would you want your final data to look like when these 2 are brought together?
Aug 03 2020 05:09 PM
| Employee Number | first | last | Name | HomePlt | Status Description | Change Type | 7 | |||||
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | CLOCK IN/PRE-TRIP | Manual | 7/29/2020 5:53 | |||||
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | IN SERVICE | Manual | 7/29/2020 6:02 | |||||
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | OUT OF SERVICE | Manual | 7/29/2020 16:02 | |||||
4560 | 61355 | Robert | Contreras | Contreras, Robert | 15 | CLOCK OUT | Manual | 7/29/2020 16:22 | |||||
Name | HomePlt | OnDuty | PunchIn | PunchOut | Shift | PPD | DOT | CLOCK IN/PRE-TRIP | IN SERVICE | OUT OF SERVICE | CLOCK OUT | Time Difference Between PunchIN and CLOCK IN PRE-TRIP | Time Difference Between CLOCK OUT and PunchOUT |
Contreras, Robert | 715 | NO | 7/29/2020 5:50 | 7/29/2020 16:58 | 14.17 | 36.67 | 36.67 | 7/29/2020 5:53 | 7/29/2020 6:02 | 7/29/2020 16:02 | 7/29/2020 16:22 | ||
7/27/2020 0:50 | 7/27/2020 13:07 | 12.28 | |||||||||||
7/28/2020 5:35 | 7/28/2020 15:48 | 10.22 | |||||||||||
7/29/2020 5:50 | 14.17 | ||||||||||||
We are trying to determine how much time is being lost by our drivers. Kronos is our time system that I get their PunchIn time. When the driver turns the key to truck, Trackit software records that as “Clock in / Pre-trip” time. Then vise-a-versa at the end of the day for Clock Out and PunchOut.
The end results we are looking for is the time difference between them and consider it as a loss of man time on the clock.
Notice please 4560 is the truck number that the driver has logged into in Trackit.. it’s possible that the driver could log into and out of different trucks thru out the day but all needs to be counted for in this calculation.
To wrap it all up in a pretty bow, a graph or some type of visual to represent these times would be awesome..
Thank you for your help with this and look forward to any help you can give.
Sincerely,
Daniel Klaes
Dispatch - Austin Division
Alamo Concrete Products
4200 Todd Ln Austin, TX 78744
Dispatch 512.444.6751
Fax 512.444.2316
dklaes@alamoconcrete.com
Aug 04 2020 11:29 PM
@dank1868 , This may not be the perfect answer, but here's my attempt at solving your problem using Power Query. I had to make a few decisions to fill in the gaps of my understanding of your industry so review carefully.
Aug 14 2020 01:29 AM
@TheAntonythank you for your help.. couple of ?'s
at the end of each day i'd like to run what you created with the new "status_raw" and "hour_raw" times,. can I do this and how? can you save the workbook so it has your query and format presented to me as a new sheet and i'll just past the data in the worksheets and new totals with calculate/.. I really appreciate your help and look forward to your next mail.