Common hours between schedules

[Donager]

I am working on a formula to identify the amount of time an employee is scheduled with their supervisor. I have converted all start and end times to numbers. For example, 1pm-10pm will be 1300-2200. The start and end time is in a separate cell.

The supervisors are setup the same. with a day over day schedule and start times.

I am working on a formula to identify if the employees schedule range is within the supervisors range, and how much.

If the employee works 1300-2200 and the supervisor works 0500-1700, I want the formula to identify how much time they share.

I converted to numbers instead of times because it is easier.

I have tried nesting various if statements and using if/and but I am not getting there yet.

Thanks for the help.

 

[mrshl9898]

Sample data with cell references and an example of your desired result would be helpful here.

 

[Nine Zero]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]emp_start[/TD]
[TD]emp_end[/TD]
[TD]emp_hours[/TD]
[TD][/TD]
[TD]sup_start[/TD]
[TD]sup_end[/TD]
[TD]sup_hours[/TD]
[TD][/TD]
[TD]shared_hours[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1300[/TD]
[TD]1800[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0500[/TD]
[TD]1700[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

From what i understand this is kinda what you are looking for (please correct me if im wrong)

Where the employee was working from 1pm - 6pm and the super was from 5am-5pm so they both shared working time from 1pm-5pm which is 4 hours

correct?

What you need is the formula for cell B9 in this example ?

 

[Donager]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]emp_start[/TD]
[TD]emp_end[/TD]
[TD]emp_hours[/TD]
[TD][/TD]
[TD]sup_start[/TD]
[TD]sup_end[/TD]
[TD]sup_hours[/TD]
[TD][/TD]
[TD]shared_hours[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1300[/TD]
[TD]1800[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]0500[/TD]
[TD]1700[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

From what i understand this is kinda what you are looking for (please correct me if im wrong)

Where the employee was working from 1pm - 6pm and the super was from 5am-5pm so they both shared working time from 1pm-5pm which is 4 hours

correct?

What you need is the formula for cell B9 in this example ?

That is exactly what I am looking for. Thank you

 

[Donager]

I sorted this out and no longer need assistance. It was a nested if/and statement. I'll post the formula as soon as I get it cleaned up for posting.