Employee Count per Interval

I think I've got it? Take a look at the attachment and see if it works for you. It relies upon the formula below in C2, filled down, then adjusted for the new columns:

=SUMPRODUCT(--(Shifts!$D$3:$D$500<=$A2+1/48),--(Shifts!$E$3:$E$500>$A2),(IF($A2+1/48>Shifts!$E$3:$E$500,Shifts!$E$3:$E$500,$A2+1/48)-IF($A2>Shifts!$D$3:$D$500,$A2,Shifts!$D$3:$D$500))*1440/30)+SUMPRODUCT(--(Shifts!$D$3:$D$500<=$A2+1/48),--(Shifts!$D$3:$D$500>Shifts!$E$3:$E$500),($A2+1/48-IF($A2>Shifts!$D$3:$D$500,$A2,Shifts!$D$3:$D$500))*1440/30)+SUMPRODUCT(--(Shifts!$C$3:$C$500>=$A2),--(Shifts!$B$3:$B$500>Shifts!$C$3:$C$500),(IF($A2+1/48>Shifts!$C$3:$C$500,Shifts!$C$3:$C$500,$A2+1/48)-$A2)*1440/30)

I experimented with some sample values and it looked like it was holding up, but play around a bit and let me know.

EDIT: It should be noted that the formula is an array formula, so it should be confirmed with Ctrl + Shift + Enter instead of the regular Enter