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$3R$2,NOT(AND($ C$3<=R$2,$D$3R$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?


+-------------------------------------------------------------------+
|Filename: Excel Sheet.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3824 |
+-------------------------------------------------------------------+

--
BFiedler
------------------------------------------------------------------------
BFiedler's Profile: http://www.excelforum.com/member.php...o&userid=27262
View this thread: http://www.excelforum.com/showthread...hreadid=467719