Need a formula schedule time spreadsheet

[analogkid12]

I am trying to create a formula for a spreadsheet that is used as an
employee schedule. I am definetly an excel novice.

Here is what I need it to do.

1. Need to create a counter in 1/2 hour increments that will tell me how
many employees are working each half hour

2. example : employee #1 working 10-1
employee #2 working 11-2

need to get results as follows:

10:00 A = 1 employee working
10:30 A = 1 employee working
11:00A = 2 employees working
11:30 A = 2 ""
12:00 N = 2"
12:30 P = 2"
1:00 P = 2"
1:30 P = 1"
I have already completed the actual schedule part complete with time
calcs and totals. Just need to know what function would work to count the
number of working employees by half hour and output that # to its own cell.

 

[Tom Ogilvy]

It would hard to say without knowing where your data is located and how it
is laid out. If you actually have entries like

working 10-1

then you should rethink how you set up your schedule.

if you actually use two cells, with start time in column B and end time in
Column C you could do

=Sumproduct(($B$2:$B$200>=F1)*($C$2:$C$200<=F1+Timevalue("00:30"))

for the example, the start of the half hour is in F1

all values are stored as time.

 

[analogkid12]

Thanks for your reply, here is some additional info, actually a cut out from
the spreadsheet. The entry of 10-1 is actually entered in 2 rows: 10:00 a in
one row and 1:00 p in the row below it. Here is a screen shot:

NAME SUN 21 MON 22

Hours Tot Hours Tot

Mark 0.0 8:00 AM 9.0
05:30 PM




Then I need to output to this at the bottom of the schedule

COVERAGE:

9:30 1 1

10:00 1 2

10:30 1 2

11:00 1 2

Will this make a difference in the formula? This has been driving me crazy,
thanks for all your help.