Counting hourly occurences between start and finish times

[tombally]

Hi

I work in a timetabling capacity at a Sydney university. What I'm trying to do in the attached sample spreadsheet is work out what hour periods classes occur in by returning a 1 if it occurs or a 0 if it doesn't.

i.e if a class runs between 09:00 and 12:30 then it occurs in the periods 9-10, 10-11, 11-12 and 12-1 but not 1-2.

or if a class runs between 16:00 and 17:00 I'd only expect it to show a 1 in the 4-5 timeslot and a 0 in everything else.

For some reason my formula works for the 9-10 slot but when I copy it over and change the values to 10 and 11 (Cell F3) it returns 0 when it should return a 1.

From here I then intend to work out how many classes per faculty occur within each hour period and how many students that represents. That bit I can do but this first bit is driving me nuts!

Any help would be appreciated.

Cheers
Tom

[Kieran]

Try this reworking. (note the headings changed for the hours!)

[Mikeopolo]

Haven't looked at the first response, but here's mine anyway!

Regards
Mike

[protonLeah]

How about:
test array.xls

[Mikeopolo]

And from that, this....

[tombally]

Thanks a lot guys that's a great help, not to mention a whole lot of new formula to learn! Mikeopolo's first example is probably the one I'll go with.

What I also need to do though is account for the classes that finish halfway through an hour period. i.e. the first class finishes at 12:30 so technically it occurs in the 12-1 hour period. Is this achievable?

[daddylonglegs]

Using Mikeopolo's setup just change the E5 formula to this

=IF(OR(TIME(E$2,0,0)>=$D5,TIME(E$3,0,0)<=$C5),0,1)

copy across and down

[tombally]

That's worked. Thanks to everyone for their help.