Calculating hours between a shift time to include core hours only

[Darren Elsom]

I need to calculate the number of hours worked between the start and end of
a working day, but only if the hours fall within a specified variable time
period.

Example: Start shift at 08:00 Finish Shift at 20:00
Variable core hours time 08:00 - 16:00 (The variable time
will be taken from 2 cells to adjust when necessary)

In this scenario, the individual worked 12 hours, however only 8 hours were
within the core hours. (Meal break time is not an issue)

The result I need is 8 hours.

The formula will also need to cope with shifts over midnight.

I have looked at various examples posted but I have not been able to
sucessfully adapt them to suit the above!!!

Any help will be greatly appreciated as I'm running out of ideas.


Thanks in atticipation,

Darren.

 

[Tom Ogilvy]

Time Overlap
The basic formula is

A1: Start 8:00
B1: End 20:00
C1: Start of Core (8:00)
D1: End of Core (16:00)


=MIN(D1,B1)-MAX(A1,C1)

to go over midnight you would need to put in some adjustments

=MIN(D1+(C1>D1),B1+(A1>B1))-MAX(A1,C1)

You will need to put in some more checks if your shift doesn't overlap the
core times.

 

[Rob van Gelder]

I have an overkill formula on my website. "Hours affected by Dates"

It handles events spanning midnight. I'll eventually get around to writing
an explanation up. Basically, for 2 events that could potentially overlap
there are 4 times involved. Let's name them ABCD.
A permutation generator (such as http://j-walk.com/ss/excel/tips/tip46.htm)
will generate 24 combinations of ABCD. You can reduce this to 12 if you know
that the times of one of those dates will be ordered chronologically. This
is what my formula is based on, 12 combinations.

For benefit of NG archiving, here's an example:

A3: 1-Jan-2004 09:15:00
B3: 1-Jan-2004 11:30:00
C3: =TIME(HOUR(A3),MINUTE(A3),SECOND(A3))
D3: =TIME(HOUR(B3),MINUTE(B3),SECOND(B3))
E1: 00:00
E2: =E1+(1/24)
E3:
=IF(OR(AND($C3<=$D3,$D3<=E$1),AND($C3>=E$2,OR($C3<=$D3,$D3<=E$1))),0,IF(AND(
$D3<=E$2,$D3>=E$1,OR($C3<=E$1,$C3>=E$2)),$D3-E$1,IF(OR(AND($C3<=E$1,$D3>=E$2
),AND($C3>=$D3,OR($C3<=E$1,$D3>=E$2))),E$2-E$1,IF(AND($C3<=$D3,$C3>=E$1,$D3<
=E$2),$D3-$C3,IF(AND($C3>=E$1,$C3<=E$2,OR($D3<=E$1,$D3>=E$2)),E$2-$C3,($D3-E
$1)+(E$2-$C3))))))+INT($B3-$A3)*(E$2-E$1)

E3 formula has no spaces
Highlight E1 to E3 and fill across
Format all the cells as HH:MM
E2 must be greater than E1 - so to measure 23:00 -> 00:00 then do 00:00 + 1
day

 

[Darren Elsom]

Tom,

Thanks, for the solution below. I have added to it to take into
consideration meal breaks and where the shift does not overlap the core
times.

Regards,

Darren.