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]

Overlap hours at a Department store like JC Penny's, however there is no clock punch in or out.	I really need help on this one!
1	Somehow it appears that the employee wrote in wrong times on the timesheets for the many departments
						I tried this formula
2	I need to calculate by department any overlapping hours.		=MIN(#REF!,#REF!)-MAX(#REF!,#REF!)
	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?
	Department 1 In	Out Department 1	In Department 2	Out Department 2	Department 3 In	Department 3 Out	Department 4 In	Department 4 Out	Department 5 In	Department 5 Out
Date
1/1/2011	13:00	19:00	6:30	10:30	14:00	16:00	14:00	16:00	12:00	14:00
1/2/2011	13:00	19:00	16:00	6:00	12:00	18:00	0:00	5:00	16:00	6:00
1/3/2011	0:00	5:00	6:30	10:30	0:00	5:00	14:00	16:00	12:00	14:00
1/4/2011	16:00	6:00	0:00	5:00	14:00	16:00	0:00	5:00	12:00	14:00
1/5/2011	16:00	6:00	6:30	10:30	6:30	8:30	14:00	16:00	21:30	11:20
1/6/2011	0:00	5:00	6:30	10:30	16:00	6:00	16:00	6:00	12:00	14:00
	Military Time
	=time plus 12 hours
	Midnight is 0:00
	Noon is 12:00

<tbody>
</tbody>

 

[Jadezircon]

Department 1 In

Out Department 1

In Department 2

Out Department 2

Department 3 In

Department 3 Out

Department 4 In

Department 4 Out

Department 5 In

Department 5 Out

Total Overlap Hours

Date

Hours

1/2/2011

13:00

19:00

16:00

6:00

12:00

18:00

0:00

5:00

16:00

6:00

9

Problem is overlapping many departments

Military Time

For Example:

On 1/2 started at 12:00 overlap at 13:00 to 18:00, and also 16:00 to 19:00 X 2 instances

=time plus 12 hours

Midnight is 0:00

Noon is 12:00

<tbody>
</tbody><colgroup><col><col><col><col><col><col span="3"><col><col><col><col><col></colgroup>

 

[Jadezircon]

Department 1 In

Out Department 1

In Department 2

Out Department 2

Department 3 In

Department 3 Out

Department 4 In

Department 4 Out

Department 5 In

Department 5 Out

Total Overlap Hours

Date

Answer Hours

1/2/2011

13:00

19:00

16:00

6:00

12:00

18:00

0:00

5:00

16:00

6:00

9

Problem is overlapping many departments

Military Time

For Example:

On 1/2 started at 12:00 overlap at 13:00 to 18:00, and also 16:00 to 19:00 X 2 instances

=time plus 12 hours

Midnight is 0:00

Noon is 12:00

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col span="4"><col><col></colgroup>

 

[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!