In the example workbook, with E3 formatted as a number, use this in E3 instead:
=24*(IF(OR(A3="",B3=""),0,B3-A3)+IF(OR(C3="",D3=""),0,IF(24*C3<=3,0,IF(24*D3<=3,1-C3,D3-C3))))
and drag down
and with F3 formatted as a number, use this in F3 instead:
=24*(IF(OR(C3="",D3=""),0,IF(24*D3>3,0,IF(24*C3<=3,D3-C3,D3))))
and drag down
EDIT:
Assumes that the PM shift ends at 3am and that the PM shift could start from midnight. Change the times in the formula to suit your shift times.
Bookmarks