Combining two IF AND formulas

[CrazyDave]

Good morning,

I have two IF(AND formulas that work fine independently, but when I combine them I get errors.
Any help to fix this issue would be appreciated.
If any one can shorten the formula as well.... that would also be appreciated....

I am using an Excel Document (2010) to track meetings at two different locations. Entering date, day, start time, finish time, location and other information. I am using the date and time information in formulas to tell me whether or not the location is open for that meeting or do I need to contact someone to get them to open up for the meeting.

So.
Column A is the Date - DD/MM/YYYY
Column B is the Day - ddd
Column C is the start time - h:mm
Column D is the finish time - h:mm
Column E is the location - either BR or RC

There is also a little table in T1 to V3 which contains the opening and closing hours of the two locations.

The formula is to be entered into Column K.

This formula tells me whether or not the meeting has been booked for a day when that location is open or closed.
=IF(E8="","",(IF(AND(OR(B8="Tue",B8="Wed",B8="Thu")*(E8="BR")),"Open",IF(AND(OR(B8="Tue")*(E8="RC")),"Open","Closed"))))

This formula tells me whether or not the location is open or closed at the time of the meeting, as some meetings are booked to start or finish outside of the opening hours.
IF(AND($C8>=$U$2,$C1<=$V$2),IF(AND($D8>=$U$2,$D1<=$V$2),"Open","Closed"))

But when I try to combine the two formulas together, I get two errors.
The rows that should fill Column K with "Closed", instead say "False".
The rows that should fill Column K with "Open" for RC, instead say "False"
=IF(E8="","",IF(AND($E8="BR")*(OR($B8="Tue",$B8="Wed",$B8="Thu")),IF(AND($C8>=$U$2,$C8<=$V$2),IF(AND($D8>=$U$2,$D8<=$V$2),"Open",IF(AND($E8="RC")*($B8="Tue"),IF(AND($C8>=$U$2,$C8<=$V$2),IF(AND($D8>=$U$2,$D8<=$V$2),"Open","Closed")))))))

Any suggestions?

Many thanks (in anticipation...)

 

[jasonb75]

You have a possible outcome missing from your second formula, which may have contributed to the problem you found.

IF(AND($C8>=$U$2,$C1<=$V$2),IF(AND($D8>=$U$2,$D1<=$V$2),"Open","Closed"))

That formula would return FALSE if C8 was < U2 or C1 was > V2. I took a guess that should be Closed, other than that, I think that this should work as you need.

=IF(E8="","",IF(OR(AND($C8>=$U$2,$C1<=$V$2,$D8>=$U$2,$D1<=$V$2),AND(B8="Tue",E8="RC"),AND(OR(B8={"Tue","Wed","Thu"}),E8="BR")),"Open","Closed"))

 

[CrazyDave]

Thanks for replying so quickly Jason,

Many thanks also for fixing my formula! I had not thought of attacking the problem that way. The way you've nested the different permutations is genius!

Muchly appreciated!

 

[CrazyDave]

Having said that..... when I put that formula into my document, I encountered another problem..... The opening hours for the two locations are slightly different... The table at T1 to V3 has the BR Open and Close at U2 and V2, and the RC Open and Close at U3 and V3.
I've tried to adjust your excellent formula to account for this, and am getting stuck again....

Unfortunately the formula is long again... and this time I've struck a simple "Value" error.... I must be getting tired, cause I can't see the problem....

=IF($E11="","",IF(AND($E11="BR",OR($B11={"Tue","Wed","Thu"}),AND(($C11>=$U$2,$C11<=$V$2),($D11>=$U$2,$D11<=$V$2))),"Open",IF(AND($E11="RC",($B11="Tue"),AND(($C11>=$U$3,$C11<=$V$3),($D11>=$U$3,$D11<=$V$3))),"Open","Closed")))

Again, any help is appreciated!

 

[jasonb75]

You had too many brackets in some places, (or not enough, depending on how you look at it).

AND(($C11>=$U$3,$C11<=$V$3),($D11>=$U$3,$D11<=$V$3)) should be AND($C11>=$U$3,$C11<=$V$3,$D11>=$U$3,$D11<=$V$3)

or AND(($C11>=$U$3),($C11<=$V$3),($D11>=$U$3),($D11<=$V$3)))

In the last version above, the extra brackets are opened and closed before each comma separating the arguments of the function. A comma between extra brackets will cause an error, as you found with your formula. It works with the part of the formula that uses {}, which signifies an array within the formula, but that is a completely different approach to logic.

I've tried to simplify the logic as much as possible by removing some parameters that appear unnecessary, from your formula, I've assumed "Closed" if the meeting starts or finishes outside of opening hours. If this is incorrect then it might need adjusting slightly.

=IF($E11="","",IF(OR(AND($E11="BR",OR($B11={"Tue","Wed","Thu"}),$C11>=$U$2,$D11<=$V$2),AND($E11="RC",$B11="Tue",$C11>=$U$3,$D11<=$V$3)),"Open","Closed"))

 

[CrazyDave]

Sweeeet!!! Thanks muchly Jason!!!
This time I've tested before responding... and it works and does exactly what I was trying to do!!!

Many, many thanks!!!