excel - Specific overtime calculation -

i'm working on simple timesheet should calculate overtime1 , overtime2, cant figure out way fix it.

normal workday on 8hrs 07:00 17:00 give no overtime (eg 07:00 16:00 or 08:00 17:00), when time exceeds 8 hours exceeding hours in cell.

my business rules are:

1) work greater 8 hours between hours of 06:00 20:00 paid overtime1.

2) work less 8 hours not generate overtime2 if after 20:00

3) work performed earlier 06:00 or later 20:00 paid @ overtime2 rate.

example 1: working 07:00 18:00 value of 3 hours of overtime1

example 2: working 14:00 22:00 generate 0 hours of overtime2.

example 3: working 05:00 21:00 give overtime1 6 hours , overtime2 2 hours (1 hour before 06:00, 1 hour after 20:00).

this pretty easy problem solve if data laid out smartly. column clock in time , column b clock out time, use formula helper determine if should use clock in time or base of 06:00:

=if(a1-floor(a1,2)<6/24,6/24,a1-floor(a1,2))

then use formula determine if should use clock out time or 20:00:

=if(b1-floor(b1,2)>20/24,20/24,b1-floor(b1,2))

then subtract 2 fractions of day, multiply 24 convert hours, subtract 8 hours of overtime1. combined in super formula looks in c1:

ot1: =if(or(isblank(a1),isblank(b1)),"",(if(b1-floor(b1,2)>20/24,20/24,b1-floor(b1,2))-if(a1-floor(a1,2)<6/24,6/24,a1-floor(a1,2)))*24-8)

remember, excel formats dates 1 = 24 hours. also, added in or(isblank(a1),isblank(b1)) statement make sure null string if 1 of values blank.

starting on overtime2, need split 2 parts: before 06:00 , after 20:00. first part checks if clock in time earlier 06:00 , if figures out how many hours. formula ends being:

=if(if(a1-floor(a1,2)<6/24,6/24,a1-floor(a1,2))<=6/24,(6/24-(a1-floor(a1,2)))*24,0)

for after 20:00, same pattern used. figure out how many parts of day logged after 20:00. final formula ends being:

=if(if(b1-floor(b1,2)>20/24,20/24,b1-floor(b1,2))>=20/24,((b1-floor(b1,2))-20/24)*24,0)

finally, figure out total number of overtime 2, add 2 formulas in d1:

ot2: =if(or(isblank(a1),isblank(b1)),"",if(if(a1-floor(a1,2)<6/24,6/24,a1-floor(a1,2))<=6/24,(6/24-(a1-floor(a1,2)))*24,0)+if(if(b1-floor(b1,2)>20/24,20/24,b1-floor(b1,2))>=20/24,((b1-floor(b1,2))-20/24)*24,0))