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

cs25001

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

cs25001

New Member
Joined
Dec 6, 2019
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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:
VBA Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,134
Messages
5,622,930
Members
415,941
Latest member
georgiana686

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top