Night shift hours

[sonar]

Hi

If I have
(start time (G13) - finish time(H13)) = total time worked (L13)

eg: 16:00 - 20:00 = 4hrs
and a portions of 1hrs falls after the 7pm, or I have
05:00 - 12:00 = 7hrs
and a portion of 2hr falls before 7am.

What formula can I look at to give me the 1 or 2hrs, as an answer
depending how much was worked before 7am and / or 7pm for L13.

AP
C.T
S.

 

[Peo Sjoblom]

For earlier than 07:00

=MAX(0,TIME(7,,)-G13)

for later than 19:00

=MAX(0,H13-TIME(19,,))

 

[Daniel.M]

Hi,

G13: StartTime ' checkin time
H13: Finish Time ' checkout time
StartDay: 7:00 ' only hours before are counted
EndDay: 19:00 ' only hours after are counted

In L13:
=H13-G13-MAX(StartDay,MIN(EndDay,MOD(H13,1)))+MAX(StartDay,MIN(EndDay,MOD(G13,1)
))+(StartDay-EndDay)*(INT(H13)-INT(G13))

Regards,

Daniel M.

 

[sonar]

Would it be possible to combine the two? As it is not possible to kno
what hours a person would work, wheather its morning or nigth.

Thanks.

 

[rbanks]

A simple formula will work: In cell L13, try:

=(IF(G13<TIME(7,0,0),TIME(7,0,0)-G13,0))+(IF(H13>TIME(19,0,0),H13-TIME(19,0,0),0))


Note: Make sure your beginning and ending times are in time formats

 

[Daniel.M]

Here's a shorter version since you're not using full timestamp (only hours in
your case):

=MAX(0,MIN(H13,StartDay)-IF(H13>G13,G13,MIN(0,G13-StartDay)))+
MAX(0,1-MAX(EndDay,G13)-IF(H13>G13,1-H13,MIN(0,EndDay-H13)))

Regards,

Daniel M.

 

[sonar]

Thank you

I will try that one.

Regards
A

 

[Daniel.M]

Hi,

Just note that it doesn't work if person works from 20:00 to 2:00 (so if the
shift can cross midnight).

Regards,

Daniel M.