Forum Discussion
copy data from 1 sheet to another if a condition is met
| 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
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.
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.