Room Availability Testing - Help please

[EizenSmith]

My self and a colleague schedule a training plan for our business on a national scale. We are trying to streamline the processes by building the facility to check the availability of rooms based on a date variable we enter. Our issues is that a training room will be used for multiple courses at different times.

From reading around the forums it may be possible to do this using array formulas however we are unable to find anything fit for purpose that we understand. Here is as far as we have gotten

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Room[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD][/TD]
[TD]required date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Room1[/TD]
[TD]01/01/2015[/TD]
[TD]05/01/2015[/TD]
[TD][/TD]
[TD]02/01/2015[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Room2[/TD]
[TD]06/01/2015[/TD]
[TD]11/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Room1[/TD]
[TD]12/01/2015[/TD]
[TD]17/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Room[/TD]
[TD]Availability[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Room1[/TD]
[TD]Booked[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Room2[/TD]
[TD]Availiable[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

we have used the following code in cell f7
=IF(AND($e$2>=b2,$e$2 <=c2),"Booked","Available")

The issue here is that we need to duplicate the formula many times for each room when it appears more than once in our plan.

Please could you also explain the mechanics of proposed solutions to aid with our learning.
Apologies we are unable to attach an example (as Mr.Excel has not given us the rights).

Thanks in advance!

 

[EizenSmith]

Bumping thread as we are still looking for a resolution.

 

[FormR]

Welcome to the forum!

You could try in cell F7 copied down.

=IF(COUNTIFS(A:A,E7,B:B,"<="&$E$2,C:C,">="&$E$2),"Booked","Available")

Excel 2010
	A	B	C	D	E	F
1	Room	Start Date	End Date		required date
2	Room1	02/01/2015	05/01/2015		02/01/2015
3	Room2	06/01/2015	11/01/2015
4	Room1	12/01/2015	17/01/2015
5
6					Room	Availability
7					Room1	Booked
8					Room2	Available
Sheet1

Cell Formulas
Range	Formula
F7	=IF(COUNTIFS(A:A,E7,B:B,"<="&$E$2,C:C,">="&$E$2),"Booked","Available")

 

[EizenSmith]

Welcome to the forum!

You could try in cell F7 copied down.

=IF(COUNTIFS(A:A,E7,B:B,"<="&$E$2,C:C,">="&$E$2),"Booked","Available")

Excel 2010

	A	B	C	D	E	F
Room	Start Date	End Date	required date
Room1
Room2
Room1
Room	Availability
Room1	Booked
Room2	Available

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]02/01/2015[/TD]
[TD="align: right"]05/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"]02/01/2015[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]06/01/2015[/TD]
[TD="align: right"]11/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]12/01/2015[/TD]
[TD="align: right"]17/01/2015[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F7[/TH]
[TD="align: left"]=IF(COUNTIFS(A:A,E7,B:B,"<="&$E$2,C:C,">="&$E$2),"Booked","Available")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That's the solution we have at the moment. However in our actual plan we have a few hundred courses and that would mean scrolling through a few hundred rows as opposed to the 20 rooms we have aviliable.

 

[FormR]

That's the solution we have at the moment. However in our actual plan we have a few hundred courses and that would mean scrolling through a few hundred rows as opposed to the 20 rooms we have aviliable.

I guess I don't understand then - the formula offered searches all entries in columns A,B and C and can be copied down as far as required.

 

[EizenSmith]

I guess I don't understand then - the formula offered searches all entries in columns A,B and C and can be copied down as far as required.

Ah, actaully i see how your suggestion works. The issues is that we are limited to Excel 2003, I should have specified that. Thank you for your help, i really wish COUNTIFS were possbile...

 

[FormR]

we are limited to Excel 2003

Then this alternative may work for you.

=IF(SUMPRODUCT(--($A$1:$A$1000=E7),--($B$1:$B$1000<=$E$2),--($C$1:$C$1000>=$E$2)),"Booked","Available")

 

[EizenSmith]

You sir are a gent and a scholar! That works perfectly. Now I just need to improve my understanding of sumproduct. Your help is very much appreciated.