Calculating Each Hour Worked by Multiple Employees to get the total Cost of Each Hour

[cs25001]

Hello all,

I currently have multiple instances of tables set up to accomplish this, but the base table headers that I currently have:

Employee ID - Entered
Hourly Pay Rate - Entered (but on another table, so really it is a reference)
Day (Sun - Sat, not date specific. Like a schedule)
Start Time
End Time
Total Hours worked

What I am trying to do:
Perfect world, I would (either on the existing table, or a separate table) display how many employees were working at any given hour. This is a 24 hour operation, so there are employees who will work, for example, from Monday into Tuesday. Even more scary Sat into Sun. I am thinking an if to return 1s or 0s from which I can either calculate their hourly rate for that hour or not.

What has worked so far:
I have been able to get the following formula to work with someone who works all of their hours on the same day:

'=IF(AND(HOUR(tbl_Schedule[@[Time In]:[Time In]])<=HOUR(tbl_Schedule[[#Headers],[0:00]]),(tbl_Schedule[@[Time In]:[Time In]]+tbl_Schedule[@[Total Hours]:[Total Hours]]/24)>=HOUR(tbl_Schedule[[#Headers],[1:00]])),1,0)

This simply does not work for someone who would work Sat 17:00 to Sun 03:30, for example, but does work for someone who works Sun 03:30 to Sun 17:00. It was here that I realized my solution is shortsighted because it does not account for offsets. I am really hitting a wall on this and am completely open to any solutions to get this working. I do not care where or how these calculations are achieved. The absolute references are also not required, but I was trying to drag this formula across nearly two hundred columns.

This caused me to think that I will need to go much bigger than I originally thought and account for each hour of each day on each row. I am fine with this solution, but still cannot conceive how to properly get the offset from what would become the reset day (Sun = Day 1, Sat = Day 7, where do the calculations for Day 7 into Day 1 go?)

Final Hope:
My headcanon is that this data will eventually go into a pivot table where each hour of the day, for each day of the week, will be able to be displayed with both the costs for each hour, or the employee count for each category. Any tips, ideas, and special bonus points for formula or vba solutions is so GREATLY appreciated.

 

[cs25001]

Solution
So I was close to this, but also apparently miles off. Essentially I just needed to add two columns to essentially act as foreign keys (c time in & out) to the reference table that I was utilizing to populate the huge column names for each hour of each day. Before I was attempting to do this within the formula. I'd bet it was something to do with syntax, but am not positive.

Final formula:

=IF(AND(tbl_Sch[@[C Time In]:[C Time In]]<=INDEX(tbl_HOD[[C Date]:[C Date]],MATCH(tbl_Sch[[#Headers],[Sun 0:00]],tbl_HOD[[ID]:[ID]],0)),tbl_Sch[@[C Time Out]:[C Time Out]]>=INDEX(tbl_HOD[[C Date]:[C Date]],MATCH(tbl_Sch[[#Headers],[Sun 1:00]],tbl_HOD[[ID]:[ID]],0))),tbl_Sch[@[Hourly Rate]:[Hourly Rate]],0)

tbl_sch is the table where this formula resides, but I was using absolute references because this had to be filled into a lot of columns and rows. tbl_Sch[@[C Time In]:[C Time In]] could easily be seen as [@c time in].
tbl_HOD is the reference table.

Additionally, I had to think outside of the box on the days. My only concern was the cost of each employee per day based upon their hours worked. Once I realized the scope fully, I simply added an additional day for the overflow:

Day 1 = Sun
Day 2 = Mon
Day 3 = Tue
...
Day 8 = Sun 2

Aggregation was done in another table.

So this works, but I always appreciate criticism or easier ways to accomplish our goals so we can help those next in line.