calculate overtime within a time calculation and omit the regular time excel

Question

trying to separate overtime and regular time into 3 cells. morning overtime, regular time, and after hours overtime in Excel. I go from site to site, and each work order gets its own row. I am subtracting end time from start time on each row. If a time goes through 08:00am, i.e. 06:00 - 10:00. then I need one cell calculation to say 2 hours overtime and the next cell calculation to say 2 hours of regular pay. same thing for the third cell except regular pay end at 5:00pm, i.e. 4:00pm to 8:00pm, 1 hours of regular pay and 3 hours of overtime. I thought that I had it with =SUMPRODUCT, but computer crashed. been three weeks and I cannot get near to a solution. Also I need the the regular pay calculation to calculate only between 8:00am and 5:00pm no matter what is in the time calculation, sometimes I work 16 hours days

Answer 1

Here's a possible solution (tested with Office365) :

Example for line 2 :

E2 : conv.start = HOUR(C2)
F2 : conv.end = HOUR(D2)
G2 : working.time = F2-E2
H2 : morning.overtime = IF(AND(D2<8,E2<=8),E2-D2,IF(AND(D2<8,E2>8),8-D2,0))
I2 : after.hours.overtime = IF(AND(D2<17,E2>17),E2-17,IF(D2>17,E2-D2,0))
J2 : regular.time = E2-D2-(SUM(G2:H2))
J10 : total (regular.time) = SUM(J2:J9)*10.95