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]

Try something like this, maybe;

=(Max(C:C)-MIN(B:B))*24

This will give the number of hours difference between the earliest Start and the latest End.

 

[Jadezircon]

Thank you for taking the time to respond, I will give it a whirl and appreciate the experts on this site!

 

[QU4487]

If you can post an example of what you want it to look like, I'm sure we can work out the other parts to your query.

 

[Jadezircon]

[TABLE="width: 978"]
<tbody>[TR]
[TD="colspan: 7"]Overlap hours at a Department store like JC Penny's, however there is no clock punch in or out.
[/TD]
[TD]I really need help on this one!
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD="colspan: 7"]Somehow it appears that the employee wrote in wrong times on the timesheets for the many departments
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]I tried this formula
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2
[/TD]
[TD="colspan: 4"]I need to calculate by department any overlapping hours.
[/TD]
[TD][/TD]
[TD="colspan: 3"]=MIN(#REF!,#REF!)-MAX(#REF!,#REF!)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 10"]Going across the spreadsheet, does anyone recommend comparing two at a time or is there a formula to calculate and identify the overlap by department?
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department 1 In
[/TD]
[TD]Out Department 1
[/TD]
[TD]In Department 2
[/TD]
[TD]Out Department 2
[/TD]
[TD]Department 3 In
[/TD]
[TD]Department 3 Out
[/TD]
[TD]Department 4 In
[/TD]
[TD]Department 4 Out
[/TD]
[TD]Department 5 In
[/TD]
[TD]Department 5 Out
[/TD]
[/TR]
[TR]
[TD]Date
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2011
[/TD]
[TD="align: right"]13:00
[/TD]
[TD="align: right"]19:00
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]10:30
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]12:00
[/TD]
[TD="align: right"]14:00
[/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2011
[/TD]
[TD="align: right"]13:00
[/TD]
[TD="align: right"]19:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]12:00
[/TD]
[TD="align: right"]18:00
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[/TR]
[TR]
[TD="align: right"]1/3/2011
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]10:30
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]12:00
[/TD]
[TD="align: right"]14:00
[/TD]
[/TR]
[TR]
[TD="align: right"]1/4/2011
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]12:00
[/TD]
[TD="align: right"]14:00
[/TD]
[/TR]
[TR]
[TD="align: right"]1/5/2011
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]10:30
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]8:30
[/TD]
[TD="align: right"]14:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]21:30
[/TD]
[TD="align: right"]11:20
[/TD]
[/TR]
[TR]
[TD="align: right"]1/6/2011
[/TD]
[TD="align: right"]0:00
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"]6:30
[/TD]
[TD="align: right"]10:30
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]16:00
[/TD]
[TD="align: right"]6:00
[/TD]
[TD="align: right"]12:00
[/TD]
[TD="align: right"]14:00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Military Time
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=time plus 12 hours
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Midnight is 0:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Noon is 12:00
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Jadezircon]

[TABLE="width: 1164"]
<tbody>[TR]
[TD][/TD]
[TD]Department 1 In[/TD]
[TD]Out Department 1[/TD]
[TD]In Department 2[/TD]
[TD]Out Department 2[/TD]
[TD]Department 3 In[/TD]
[TD]Department 3 Out[/TD]
[TD]Department 4 In[/TD]
[TD]Department 4 Out[/TD]
[TD]Department 5 In [/TD]
[TD]Department 5 Out[/TD]
[TD]Total Overlap Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Hours [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2011[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]5:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]9[/TD]
[TD]Problem is overlapping many departments[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Military Time[/TD]
[TD][/TD]
[TD]For Example:[/TD]
[TD][/TD]
[TD="colspan: 6"]On 1/2 started at 12:00 overlap at 13:00 to 18:00, and also 16:00 to 19:00 X 2 instances[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=time plus 12 hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Midnight is 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Noon is 12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col span="3"><col><col><col><col><col></colgroup>[/TABLE]

 

[Jadezircon]

[TABLE="width: 983"]
<tbody>[TR]
[TD][/TD]
[TD]Department 1 In[/TD]
[TD]Out Department 1[/TD]
[TD]In Department 2[/TD]
[TD]Out Department 2[/TD]
[TD]Department 3 In[/TD]
[TD]Department 3 Out[/TD]
[TD]Department 4 In[/TD]
[TD]Department 4 Out[/TD]
[TD]Department 5 In [/TD]
[TD]Department 5 Out[/TD]
[TD]Total Overlap Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Answer Hours[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1/2/2011[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]5:00[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]6:00[/TD]
[TD] 9[/TD]
[TD]Problem is overlapping many departments[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Military Time[/TD]
[TD]For Example:[/TD]
[TD][/TD]
[TD="colspan: 8"]On 1/2 started at 12:00 overlap at 13:00 to 18:00, and also 16:00 to 19:00 X 2 instances[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]=time plus 12 hours[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Midnight is 0:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Noon is 12:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col><col span="4"><col><col></colgroup>[/TABLE]

 

[Jadezircon]

Re: How to calculate overlapping hours this is complicated I need to find out the number of hours per day

Hello, this is a doozy, should I calculate department overlap by one department compared to another at a time or is there a formula for one row? Thanks, Jadezircon from Alaska

 

[QU4487]

The idea of my formula was sound. I'd just failed to realise which way the data was going. My teachers always said I should take more time to read the question!

Here you go;

=(MAX(C3,E3,G3,I3,K3)-MIN(B3,D3,F3,H3,J3))*24

IF you need to know how many hours are worked in each department, I'd be tempted to add extra column to the right of the out time. Then use a similar formula;

=(C2-B2)*24

Hope this helps.

If you only have 5 departments, place this in column L.

 

[Jadezircon]

Thank you so very much, why do I multiply by 24? Is that and imbedded question to meet the 24 hours in a day? Jade zircon in Alaska Thank you so much for responding!