Night differential

Here is another alternative that makes a 24 hour table, which may be hidden for aesthetic purposes and/or moved to make way for other columns (to calculate pay for example), divided into 30 minute time intervals and is populated with the formula:

Formula:

=IF(ROUND($C2,6)=ROUND(I$1,6),"s",IF(ROUND($D2,6)=ROUND(I$1,6),"f",IF(OR(H2="s",H2="d"),"d","")))

Two columns then calculate the number of hours worked in the 8:00 - 10:00 and 10:00 - 6:00 time frames using the following formulas respectively:

Formula:

=SUMPRODUCT((($I2:$BD2="d")+($I2:$BD2="s"))*($I$1:$BD$1>=F$8)*($I$1:$BD$1<F$9))/2

Formula:

=SUMPRODUCT((($I2:$BD2="d")+($I2:$BD2="s"))*(($I$1:$BD$1>=G$8)+($I$1:$BD$1<G$9)))/2

Note that column H is populated with the formula:

Formula:

=IF(OR(BD2="s",BD2="d"),"d","")

Let me know if you have any questions.