Rota with 4 shifts - calculate hours?

Rota example.xlsx

I'm looking for ideas on how to solve this particular problem. I need a formula to calculate cell O3 based on the start and finish times of the 4 shifts. At the moment i have it working but only if each individual shift has a start and end time filled in (person 1). I need it to work for example, if the start time of the morning shift and the end time of the afternoon shift are the only times filled in (person 2).

As a side problem also, the cell P0 counts hours where any of the cells D3, G3, J3 and M3 contain the phrase "DCN"

Any ideas?

Thanks in advance..

Screen Shot 2014-11-01 at 17.55.06.png

Originally Posted by Richard Buttrey

Hi,

One way

Formula:

=E3-C3+H3-F3+K3-I3+N3-L3+IF(N3<L3,1,0)

This seems to work! Thank you.

popipipo's version worked ok too but it didn't allow for the end time being in the early hours of the next day.

Thanks for the replies.