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]

	1	2	3	4	5	6	7	8	9
A	emp_start	emp_end	emp_hours		sup_start	sup_end	sup_hours		shared_hours
B	1300	1800	5		0500	1700	10		4

<tbody>
</tbody>

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]

	1	2	3	4	5	6	7	8	9
A	emp_start	emp_end	emp_hours		sup_start	sup_end	sup_hours		shared_hours
B	1300	1800	5		0500	1700	10		4

<tbody>
</tbody>

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.