Night shift hours

  • Thread starter Thread starter sonar
  • Start date Start date
S

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.
 
For earlier than 07:00

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

for later than 19:00

=MAX(0,H13-TIME(19,,))
 
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.
 
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.
 
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
 
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.
 
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.
 
Back
Top