Formula to count number of employees staffed per 30 min interval

Hello,

I am looking for a formula to count the number of employees staffed per 30 min interval based on a list of start and stop times.

Ex.. based on the table below, how many are staffed at 6:30 am and how many at 7:00 am...etc...?

This did not copy over correctly.. in the 1st row, 6:30 am is the start time and 3:00 PM is the end time


EMP A 6:30 AM 3:00 PM
EMP B 6:30 AM 3:00 PM
EMP C 6:30 AM 3:00 PM
EMP D 6:30 AM 3:00 PM
EMP E 6:30 AM 3:00 PM
EMP F 6:30 AM 3:00 PM
EMP G 6:30 AM 3:00 PM
EMP H 8:00 AM 4:30 PM
EMP I 8:30 AM 5:00 PM
EMP J 8:30 AM 5:00 PM
EMP K 8:30 AM 5:00 PM
EMP L 8:30 AM 5:00 PM

I have seen this before on a sheet that someone else created, and i believe it was a version of a countifs, but I'm not able to replicate it.

Thanks,

Don

If the end time was 6:30 AM should that be counted within the 6:30 AM interval?

I'm assuming you want a concurrent count.

Data Range

	A	B	C	D	E	F
1	------	------	------	------	------	------
2	EMP A	6:30 AM	3:00 PM		6:30 AM	7
3	EMP B	6:30 AM	3:00 PM		7:00 AM	7
4	EMP C	6:30 AM	3:00 PM		7:30 AM	7
5	EMP D	6:30 AM	3:00 PM		8:00 AM	8
6	EMP E	6:30 AM	3:00 PM		8:30 AM	12
7	EMP F	6:30 AM	3:00 PM
8	EMP G	6:30 AM	3:00 PM
9	EMP H	8:00 AM	4:30 PM
10	EMP I	8:30 AM	5:00 PM
11	EMP J	8:30 AM	5:00 PM
12	EMP K	8:30 AM	5:00 PM
13	EMP L	8:30 AM	5:00 PM

This formula entered in F2 and copied down:

=SUMPRODUCT(--(E2>=B$2:B$13),--(E2<C$2:C$13))

You're welcome. Thanks for the feedback!

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread