COUNTIF between two times

Hi guys,

Hope you can help with this one.

I have a rota that has a big list of names in the rows and days of the week in the columns.

Each cell then says what shift/hours they worked for that day. So for example 08:30 - 15:30.

What I want to do is track how many staff we had in at half an hour intervals.

SO an example output would look like:
08:00 - 102
08:30 - 122
09:00 - 121
09:30 - 125
etc

Where each time represents how many people were working at that particular time.

So far I have tried splitting each person unto two additional columns using LEFT & RIGHT to get their start and stop time, then using a COUNTIFS and TIME function but I can't seem to get it working.

I have attached a simple example of what the data looks like (1st tab) and what the results should look like (2nd tab).

Is anyone able to help me?

Does it make sense to give dummy data that is not correctly calculated? No. The dummy data in your workbook should show correct and manually calculated example results.

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Please try at B2 Press Ctrl+Shift+Enter and copy down

=SUM(IFERROR((--SUBSTITUTE(LEFT(Rota!$B$2:$B$21,5),".",":")<=A2)*(--SUBSTITUTE(RIGHT(Rota!$B$2:$B$21,5),".",":")>A2),))

Eg B3 "08.30-14.30"

=--SUBSTITUTE(LEFT(Rota!B3,5),".",":")

=--SUBSTITUTE(LEFT("08.30-14.30",5),".",":") =--SUBSTITUTE("08.30",".",":") =--"08:30" is TIME 8:30

For RIGHT right will get TIME 14:00

If Time in column A is between LEFT and RIGHT will get 1, if not will get 0
Iferror for the Data in Rota that doesn't have time will result 0


=SUM(IFERROR((--SUBSTITUTE(LEFT(Rota!$B$2:$M$21,5),".",":")<=A2)*(--SUBSTITUTE(RIGHT(Rota!$B$2:$M$21,5),".",":")>A2),))

Change from Post #5 from B to M to for all date