Formula Consolidation: Conditional Total Labor Hours with Clock-in/Clock-out Data

[akicker]

I am working with clock-in and clock-out data for various departments, with the goal of calculating Labor Hours for any given Department and Time Period. I am seeking help in consolidating my current method, preferably without Power BI or related tools. But I am open to all suggestions and whatever is most practical. The problem is the workbook gets very large and slow when I incorporate more and more data (e.g. 50,000+ work shift records, with 30+ formulas for each record).

My tool is accurate and works great for smaller data sets. I didn't write out explicit Excel statements, but rather explained generally what types of formulas I used.

Below is a snapshot of what I am currently doing. The first 4 Columns are raw data. Column E, Clock-Out, is calculated as Clock-in + Hours Worked. Then Column F and onward for each hour has the following algorithm in Excel syntax in every cell:

t = current hour (Column Header in Row 1)
Ci = clock-in time
Co = clock-out time
h1 = Excel's value for 1 hour (~0.041667)

if Ci <= t + h1 <= Co + h1:
    min(  1,
          (t + h1 - Ci) / h1,
          (Co - t) / h1
        )
else:
    0

This gives us the portion of the hour worked, for every hour:

	A	B	C	D	E	F	G	H	I	J	K	...	AC	AD	AE	AF	AG	AH	AI
1	Dept.	Date	Clock In	Hours Worked	Clock Out	00:00	01:00	02:00	03:00	04:00	05:00	......	23:00	24:00	25:00	26:00	27:00	28:00	29:00
2	Bar	1/1/17	01:15	3.5	04:45	-	0.75	1.00	1.00	0.75	-	...	-	-	-	-	-	-	-
3	Bar	1/1/17	02:30	3.25	05:45	-	-	0.50	1.00	1.00	0.75	...	-	-	-	-	-	-	-
4	Restaurant	1/2/17	23:15	5	28:15	-	-	-	-	-	-	...	0.75	1	1	1	1	0.25	-
	...	...	...

<tbody>
</tbody>

As you can see, shifts starting on one day and ending the next, stay on one line, and I take care of these in the following summary:

	A	B	C	D	E
1	Choose a Department:	Bar
2	Ref. Current Day	Ref. Previous Day	Date	Hour	Labor Hours
3	F:F	AD:AD	1/1/17	00:00	-
4	G:G	AE:AE	1/1/17	01:00	0.75
5	H:H	AF:AF	1/1/17	02:00	1.50
6	I:I	AG:AG	1/1/17	03:00	2.00
...

<tbody>
</tbody>

I am using two SUMIFS statements with INDIRECT in the Labor Hours Column E here in order to reference the appropriate rows and columns from the previous table with every shift. This way we get all hour portions where the shift started on that day, along with any hours where the shift started on the previous day.

Then after all this, I have a final Summary page where I just choose a time period, like a certain week, and then it flows up nicely to show all labor hours for each day in that week.

How can I cut down this process? Am I missing some useful aspect of Excel's normal pivot tables in order to streamline these calculations? I really want to avoid using hundreds of thousands of IF statements, MIN statements, and then thousands of double SUMIFS with INDIRECT, etc...

I appreciate any insight you can give

 

[akicker]

Bump

 

[SpillerBD]

For your desired output, you would need to Unpivot the Calculated Hour section of your table.

 

[akicker]

For your desired output, you would need to Unpivot the Calculated Hour section of your table.

I am actually not using Pivot tables anywhere right now.