COUNTIF time interval falls between set of time ranges

[eyoonbbj]

Hi Everyone,

I have a list of classes with the day along with start & end times in one spreadsheet.
The format looks like this:
M 8:00 AM - 9:50 AM

T 12:30 PM - 2:20 PM
M 9:00 AM - 10:15 AM
F 7:00 AM - 1:00 PM
In another spreadsheet i have a table like this one below. I want to know how many classes occur on Mondays between 6:00 am and 6:29 am,6:30 am - 6:59 am,7:00 am - 7:29 am, etc.
M T W R F
6:00 AM
6:30 AM
7:00 AM
7:30 AM
8:00 AM
8:30 AM
9:00 AM
9:30 AM
10:00 AM
10:30 AM
11:00 AM
11:30 AM
12:00 PM
12:30 PM
1:00 PM
1:30 PM
2:00 PM
2:30 PM
3:00 PM
3:30 PM
4:00 PM
4:30 PM
5:00 PM
5:30 PM
6:00 PM
6:30 PM
7:00 PM
7:30 PM
8:00 PM
8:30 PM
9:00 PM
9:30 PM
10:00 PM
10:30 PM
11:00 PM
11:30 PM
12:00 AM
I appreciate your help!
Thank you!

 

[rilzniak]

Assuming your start time is in cell A1, your end time is in cell B1, and your range of times is in A3:A40, here is your formula: =SUMPRODUCT(--($A$3:$A$40>=A1),--($A$3:$A$40<=B1))

 

[eyoonbbj]

Hi rilzniak,

Thanks for your reply! Perhaps I should provide a sample. Below I have a course schedule. Below the course schedule is what I'd like to achieve except my x axis would have half hour increments. Please let me know if you would like additional information. Thank you so much for helping me!!!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Room[/TD]
[TD="align: center"]Course Number[/TD]
[TD="align: center"]Course Name[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]TH[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]Start Time[/TD]
[TD="align: center"]End Time[/TD]
[/TR]
[TR]
[TD]313
[/TD]
[TD]NU615[/TD]
[TD]ADVANCED HEALTH ASSESSMENT[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6:00 AM[/TD]
[TD]9:00 AM[/TD]
[/TR]
[TR]
[TD]310[/TD]
[TD]NU455[/TD]
[TD]NURSING SYNTHESIS AND CAPSTON[/TD]
[TD]M[/TD]
[TD]T[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[/TR]
[TR]
[TD]312[/TD]
[TD]LAB[/TD]
[TD]OPEN LAB[/TD]
[TD][/TD]
[TD][/TD]
[TD]W[/TD]
[TD]TH[/TD]
[TD][/TD]
[TD]12:00 PM[/TD]
[TD]6:00 PM[/TD]
[/TR]
[TR]
[TD]306
[/TD]
[TD]NU220[/TD]
[TD]HEALTH ASSESSMENT[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7:00 AM[/TD]
[TD]1:00 PM[/TD]
[/TR]
[TR]
[TD]311[/TD]
[TD]TUTOR[/TD]
[TD]TUTORING - NURSING[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]W[/TD]
[TD][/TD]
[TD]F[/TD]
[TD]1:00 PM[/TD]
[TD]6:00 PM[/TD]
[/TR]
[TR]
[TD]313[/TD]
[TD]NU440[/TD]
[TD]WOUND CARE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]TH[/TD]
[TD][/TD]
[TD]10:00 AM[/TD]
[TD]2:00 PM[/TD]
[/TR]
[TR]
[TD]312[/TD]
[TD]NU266[/TD]
[TD]BASIC SKILLS LAB[/TD]
[TD][/TD]
[TD]T[/TD]
[TD]W[/TD]
[TD][/TD]
[TD][/TD]
[TD]9:00 AM[/TD]
[TD]11:00 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[rilzniak]

So you're trying to find how many 30 min increments are between 6:00 AM and 9:00 AM, and fill down? And then populate those results on two separate graphs?

 

[eyoonbbj]

Sorry if I'm causing any confusion. I would like to emulate the graph above, only the horizontal axis would be in 30 min intervals - 6:00 am, 6:30 am, 7:00 am, 7:30 am, 8:00 am, 8:30 am, 9:00 am, 9:30 am,10:00 am, 10:30 am, etc. all the way to 12:00 am. The vertical axis will be the number of classes. So I would like to know, how many classes occur on any given day and time of day. Hopefully that makes better sense.