Breaking down the labor cost into per minute under the hourly heady

[hpo2509]

Hi Everyone,

I have these data that shows the start of time and end of time. We need to know the actual time we charge on a per minute on that certain hour/time.

Job title	Location	Clock in date	Clock in time	Clock out date	Clock out time	Regular hours	Hourly rate
Manager	TOR	9/1/2022​	12:00:10​	9/1/2022​	19:10:29​	7.17​	9.9958​
Bar Staff	TOR	9/1/2022​	18:56:30​	9/2/2022​	0:36:03​	5.67​	8.1957​

Output should be.
Full hourly rate for the services rendered for the full hour and prorated per minute on the times not full hour is rendered e.g
@18:00 time period a new staff came in and worked for 56minutes this will be converted to minute rate then added to the full hour rate of the existing staff.
@19:00 time period the first staff left but clocked out with 10 minutes so this will be added to the full hour of the staff that came in @18:00

Table should be this

Date/Time	11:00:00​	12:00:00​	13:00:00​	14:00:00​	15:00:00​	16:00:00​	17:00:00​	18:00:00​	19:00:00​
9/1/2022​		9.9958	9.9958	9.9958	9.9958	9.9958	9.9958	17.64582	9.86173

Any leads to to this I had the sumifs formula but was able to get only the per hour charges and can't get the prorated per minute charge.

Thanks in advance

 

[JamesCanale]

I put my answer in row 7. Below it, I have the extra calculations spread out a bit to be more readable.

MrExcelPlayground14.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Title	Location	Clock in Date	Clock in Time	Clock out Date	Clock out Time	Regular Hours	Hourly Rate
2	Manager	TOR	9/1/2022	12:00:10	9/1/2022	19:10:29	7:10	9.9958
3	Bar Staff	TOR	9/1/2022	17:56:30	9/2/2022	0:36:03	6:39	8.1957
4
5
6	Date	Location	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00
7	9/1/2022	TOR	0	9.968034	9.9958	9.9958	9.9958	9.9958	10.47388	18.1915	9.942188	8.1957	8.1957
8
9			12:00:10	12:00:10	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	20:00:00	21:00:00
10			12:00:00	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	19:10:29	19:10:29	19:10:29
11			0:00:00	0:59:50	1:00:00	1:00:00	1:00:00	1:00:00	1:00:00	1:00:00	0:10:29	0:00:00	0:00:00
12			0	9.968034	9.9958	9.9958	9.9958	9.9958	9.9958	9.9958	1.746488	0	0
13			17:56:30	17:56:30	17:56:30	17:56:30	17:56:30	17:56:30	17:56:30	18:00:00	19:00:00	20:00:00	21:00:00
14			12:00:00	13:00:00	14:00:00	15:00:00	16:00:00	17:00:00	18:00:00	19:00:00	20:00:00	21:00:00	22:00:00
15			0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:00:00	0:03:30	1:00:00	1:00:00	1:00:00	1:00:00
16			0	0	0	0	0	0	0.583088	9.9958	9.9958	9.9958	9.9958
Sheet9

Cell Formulas
Range	Formula
G2:G3	G2	=(E2+F2)-(C2+D2)
D6:M6	D6	=C6+1/24
C7:M7	C7	=(MAX(0,MIN($A7+C$6+1/24,$E$2+$F$2)-MAX($A7+C$6,$C$2+$D$2))*24*$H$2)+(MAX(0,MIN($A7+C$6+1/24,$E$3+$F$3)-MAX($A7+C$6,$C$3+$D$3))*24*$H$3)
C9:M9	C9	=MAX($A7+C$6,$C$2+$D$2)
C10:M10	C10	=MIN($A7+C$6+1/24,$E$2+$F$2)
C11:M11,C15:M15	C11	=MAX(0,C10-C9)
C12,C16	C12	=(C11*24*$H$2)
D12:M12,D16:M16	D12	=D11*24*$H$2
C13:M13	C13	=MAX($A7+C$6,$C$3+$D$3)
C14:M14	C14	=MIN($A7+C$6+1/24,$E$3+$F$3)

 

[hpo2509]

Awesome James, you even got up to the seconds. Quick one again if there will be additional lines let's say we'll be hiring new staff and it'll be more lines. Is it possible to have it for the whole month? Was thinking of a dynamic formula if there'll be more lines. But for now I'll work on the formula you gave as we have 2 employees for now.

I put my answer in row 7. Below it, I have the extra calculations spread out a bit to be more readable.

 

[JamesCanale]

This is one where 365 would help. You'd use FILTER to pull only the data you are interested - a particular day and location - from your whole data set, then work on that.

One of the problems, array formulas get broken by MAX or MIN - so they are harder to work with.

But at the end of the day, this is a thing that could be done - the formulas just get a bit unmanagible to look at.