copy data from 1 sheet to another if a condition is met

Question

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

theantony · Answer

dank1868&nbsp;, could use a bit more explanation. For example,&nbsp;Here's one employee's details from Sheet1:&nbsp;&nbsp;&nbsp;Employee NumberfirstlastName&nbsp;Status DescriptionChange Type7456061355RobertContrerasContreras, Robert15CLOCK IN/PRE-TRIPManual2020-07-29 05:53:49456061355RobertContrerasContreras, Robert15IN SERVICEManual2020-07-29 06:02:56456061355RobertContrerasContreras, Robert15OUT OF SERVICEManual2020-07-29 16:02:10456061355RobertContrerasContreras, Robert15CLOCK OUTManual2020-07-29 16:22:10&nbsp;Here's the same employee's information from Sheet2:NameHomePltOnDutyPunchInPunchOutShiftPPDDOTContreras, Robert715YES2020-7-29 5:50&nbsp;14.1736.6736.67&nbsp;&nbsp;&nbsp;2020-7-27 0:502020-7-27 13:0712.28&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2020-7-28 5:352020-7-28 15:4810.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2020-7-29 5:50&nbsp;14.17&nbsp;&nbsp;&nbsp;How would you want your final data to look like when these 2 are brought together?&nbsp;&nbsp;

dank1868 · Answer

dank1868&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Employee NumberfirstlastNameHomePltStatus DescriptionChange Type7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;456061355RobertContrerasContreras, Robert15CLOCK IN/PRE-TRIPManual7/29/2020 5:53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;456061355RobertContrerasContreras, Robert15IN SERVICEManual7/29/2020 6:02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;456061355RobertContrerasContreras, Robert15OUT OF SERVICEManual7/29/2020 16:02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;456061355RobertContrerasContreras, Robert15CLOCK OUTManual7/29/2020 16:22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;NameHomePltOnDutyPunchInPunchOutShiftPPDDOTCLOCK IN/PRE-TRIPIN SERVICEOUT OF SERVICECLOCK OUTTime Difference Between PunchIN and CLOCK IN PRE-TRIPTime Difference Between CLOCK OUT and PunchOUTContreras, Robert715NO7/29/2020 5:507/29/2020 16:5814.1736.6736.677/29/2020 5:537/29/2020 6:027/29/2020 16:027/29/2020 16:22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/27/2020 0:507/27/2020 13:0712.28&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/28/2020 5:357/28/2020 15:4810.22&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;7/29/2020 5:50&nbsp;14.17&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;We are trying to determine how much time is being lost&nbsp; by our drivers. Kronos is our time system that I get their PunchIn time. When the driver turns the key to truck, Trackit &nbsp;software records that as “Clock in / Pre-trip” time. Then vise-a-versa at the end of the day for Clock Out and PunchOut.&nbsp;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.&nbsp;Notice please 4560&nbsp; 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.&nbsp;To wrap it all up in a pretty bow, a graph or some&nbsp; type of visual to represent these times would be awesome..&nbsp;Thank you for your help with this and look forward to any help you can give.&nbsp;Sincerely,Daniel KlaesDispatch&nbsp; - Austin DivisionAlamo Concrete Products4200 Todd Ln &nbsp;Austin, TX&nbsp; 78744Dispatch 512.444.6751Fax 512.444.2316dklaes@alamoconcrete.com&nbsp;&nbsp;&nbsp;

theantony · Answer

dank1868&nbsp;, 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.&nbsp;

dank1868 · Answer

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.

Forum Discussion

copy data from 1 sheet to another if a condition is met

4 Replies

Resources

	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

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