I am struggling to work out the correct formula to work out the following problem: I am trying to work out how many day hours and how many night hours make up one shift. E.g

Start time 1pm (date 8/7/12)
End time 1am (date 9/7/12)
Shift Length = 12 hours
Day Hours (Day hours are classed between:7am - Midnight) 6 hours
Night Hours (Night hours are classed between:Midnight - 7am) 6 hours

A1 = Start time
B1 = End time
C1 = Day Hours
D1 = Night Hours
E1 = Shift Length

Please help
x