I am building a spreadsheet with an employees start time, end time and lunch start and end times. I would like to take that information and add up how many employees are working during a specific half hour time block through out the day on any given day during the week.

Example:

Name Monday Start Lunch Start Lunch End Monday End Tuesday Start...
Last, First 7:00 9:00 10:00 4:00
Last, First 8:00 11:00 12:00 5:00
Last, First 16:00 20:00 21:00 00:00


Then for each half hour on monday I would like to know how many employees are working.

6:30 7:00 7:30 8:00 8:30 9:00 9:30
0 1 1 2 2 2 2 etc.

Here is the formula that I came up with:=IF(AND($B$3<>"",$B$3<=R$2,$E$3>R$2,NOT(AND($C$3<=R$2,$D$3>R$2))),1, 0)

B3 Start Time
E3 End Time
C3 Lunch Start Time
D3 Lunch End Time
R2 Half Hour time block (6:30)

This formula works for day 1st and 2nd shift people..but if their start time is at 21:00 and their end time is 07:00 it does not work....(becaue of how the greater than and less than signs are set up)

Is there a better way to do this?