Employee and Supervisor #of hours worked together every week

[spranger24]

Hello everyone, I'm hoping to tap into some expert knowledge that I do not have.

I'm looking to find a formula/formulas to calculate the number of hours each employee would work together with their supervisor each week. The example I'll give is below showing what I'd like to see.

The ultimate goal is to try and figure out the best supervisor for each employee based on their schedule.

The equals sign is used to designate days off in the "Days" column.

Example:

Employee 1 is assigned to Supervisor 1 - they would work 42.5 hours together each week since the employee works an 8.5 hour shift and the supervisor works a 9 hour shift.

Employee 2 is assigned to Supervisor 2 and they work 34 hours together each week.

If we assigned Employee 1 to supervisor 2, they would only work 25.5 hours together each week.

Hope this helps!

						SUP 1	SUP 2	SUP 3
						6AM-3PM	6AM-3PM	7AM-4PM
name	Hours	Start	Stop	Days	Supervisor	==SMTWR	FY==TWR	FYSMT==
emp 1	06:00-14:30	06:00	14:30	==SMTWR	SUP 1	42.50	25.50	22.50
emp 2	06:30-15:30	06:30	15:30	F==MTWR	SUP 2	34.00	34.00	25.50

I appreciate any suggestions.

Thanks!

 

[BSALV]

this is how it works for 1 day, so multiply with your number of days in a week

Map1
	B	C	D	E	F	G	H
6					sup1	sup2	sup3
7					6:00	6:00	7:00
8					15:00	15:00	16:00
9	emp1	6:00	14:30		8:30	8:30	7:30
10	emp2	6:30	15:30		8:30	8:30	8:30
Blad2

Cell Formulas
Range	Formula
F9:H10	F9	=MAX(0,MIN(F$8,$D9)-MAX(F$7,$C9))