Spread() R function in Excel

Question

I am trying to make a table from two tables that tells me the time between intervals. Let me explain better:

Chart A - Shows the time when the employee punched in in the morning and again after lunch

Chart B - Shows the time when the employee punched out in the morning before lunch and at the end of the day.

I want to be able to make a table that tells me for each employee for each day how many hours they have been at work between morning entrance and morning exit and then between afternoon entrance and afternoon exit. I was able to make this very easily using R with the spread() function: Please excuse the Spanish column headers

What I want to know is if there is a spread() function in excel or if there is a combination of formulas I can use to do something similar. If someone could give me some advice on the right direction to go in.

Answer 1

Here I stacked your tables on top of each other. As long as columns are the same, this is fine.

In Columns I, J and K input your ID/Name/Date values. Remove duplicates. Either use a formula or copy/paste to get that data together.

Morning Entrance, cell L3 formula:

=INDEX($C$3:$C$30,MATCH(1,($I3=$A$3:$A$30)*($K3=$E$3:$E$30)*(L$2=$D$3:$D$30),0))

In the formula above, we're matching the ID, Date and Period to the appropriate array(s).

While in the formula, make it an array formula by pressing CTRL+ SHFT + ENTER. That will get the curly brackets around it. Then drag that formula across through Afternoon exit. So you'll have this formula in 4 columns. Drag it down.

Period1_diff, cell P3 formula:

=M3-L3

Period2_diff, cell Q3 formula:

=O3-N3

Total_Hours, cell R3 formula:

=SUM(P3:Q3)

Drag down your formulas and appropriately format your cells (use custom format, such as hh:mm:ss for those last 3 columns).

If your tables are not stacked on top of each other, then simply adjust the formulas as needed.