How to calculate overlapping hours between departments

[Jadezircon]

Hello,

I have an employee working 37 hours per day by accident, (even though there are 24 hours) I used military time.

Column A is the month/day/year
Column B is the Department 1 In Time
Column C is the Department 1 Out Time

Altogether there are about 15 departments with In and Out times that overlap. Is there a formula to run across multiple In and Out (Column's B and C) that will calculate the overlap? I need the earliest time and the latest time, then the overlap hours, then apply the rate of pay.

Help!

 

[QU4487]

Just found a flaw with my formula. Just clocked that you work across days. As such you need to also input the day, month and year into the In and Out cells. You don't have to see, but they do have to be there. Ie you can format the cells to show only HH:MM. But when you input the data it needs to be MM/DD/YY HH:MM. Hope that makes sense.

You're correct. The *24 is used to return the results in hours.

 

[Jadezircon]

Well, I think I am going to have to just find the 24 hour days, and everything after that is more than 24 hours and will need to be calculated separately. I will just compare one department to the next, the problems include the lunches and breaks. Just one at a time across the spreadsheet. Three years, sorry I inherited the project!

 

[QU4487]

Ah, working with old data. That is annoying. You could still use my formula, as it is. But also add a conditional format to the column, that highlights every cell where the end time is before the start time. That will at least make it easier to spot when the formula isn't going to work.

 

[Jadezircon]

Good news, I am starting to figure this out, there are actually 12 jobs held, none that clock in. Currently I am looking at over 24 hours per day, like 37 on the average over 3 years. I believe that three jobs overlapping are at night and the employee is sleeping, thus able to maintain the two day jobs. I figured out I need to begin at Midnight every day as the base. Hot dog! There are 4 Overlap solutions and 4 non Overlap solutions when comparing two departments shown below: Ways in which (a,b) can overlap with (x,y) shown here: x is between a and b, a is between x and y, x is between both a and b, and a is between x and y. This if for the overlaps that need to have the amount of hours calculated. For hours without overlap there are also four possibilities, x to y and y is < a, or b < x these need to be identified as non overlaps. I realized the base should start at 0:00 or midnight, the beginning of each day.