Help on calculating extra pay

[H@im]

I hope someone could help me with this riddle.
Extra pay is given when start and end time is within 8:00 AM to 4:00 PM, so that's a total of 8 hrs
e.g.
1. 5:00 AM - 4:00 PM - should have 8hrs full extra pay
2. 3:00 PM - 2:00 AM - should have get only 1hr extra pay
3. 12:00 AM - 9:00 AM - should get only 1hr extra pay
4 . 7:45 AM - 6:45 PM - should have 8hrs full extra pay

please see attached for the format needed. Thanks!

[JBeaucaire]

Assuming:

A1: 3:00 PM
B1: 2:00 AM

C1: =IF((MEDIAN("8:00 AM", "4:00 PM", B1)-MEDIAN("8:00 AM", "4:00 PM", A1))*24<0, (1-MEDIAN("8:00 AM", "4:00 PM", B1)-MEDIAN("8:00 AM", "4:00 PM", A1))*24, (MEDIAN("8:00 AM", "4:00 PM", B1)-MEDIAN("8:00 AM", "4:00 PM", A1))*24)


This shorter version almost works:

C1: =((MEDIAN("8:00 AM", "4:00 PM", B1)-MEDIAN("8:00 AM", "4:00 PM", A1))*24)+(A1>B1)

...but is missing the example above. It gets all your other examples right. Still musing over that.

http://screencast.com/t/7D4JqbskQK

[daddylonglegs]

You need to multiply the last part by the length of the period, Jerry, (8 AM to 4 PM), e.g.

=(MEDIAN("8:00 AM", "4:00 PM", B2)-MEDIAN("8:00 AM", "4:00 PM", A2))*24+(A2>B2)*8

or explicitly linking the start/end times

=(MEDIAN("8:00 AM", "4:00 PM", B2)-MEDIAN("8:00 AM", "4:00 PM", A2)+(A2>B2)*("4:00 PM"-"8:00 AM"))*24

[JBeaucaire]

Awesome as always, DDL. Thanks.

[H@im]

Thank you Jbeaucaire and Daddlylonglegs! You're amazing! the first one Jbeaucaire's formula worked for my file. However with start time of 6pm - 3am, i got this 2.66454E-15. How should I get rid of this? please see attached file again provided both formulas given... thank you! EXTRA PAY (1).xls

[H@im]

I tried the other formula Daddylonglegs gave! it worked! thank you! EXTRA PAY (1).xls