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

[dank1868]

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

[#2]

@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? 

[#3]

@dank1868 

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

[#4]

@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. 

[#5]

@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.