i need a formula that can check if there are overlapping dates where we have 2 houses available for rent. all bookings are shown in a single list and 2 identical dates are fine ie 2 identical bookings such as ... in-13/09/11 out-15/09/11 is fine because we can put first renter in one house and the other renter in the other house.

from various forums i can see that a common way to check overlapping bookings is to use a variation of the following formula =SUMPRODUCT(($A2<$B$2:$B$11)*($B2>$A$2:$A$11))>=3.

from the list below you can see that it correctly indicates an overlapping booking in first 3 lines (formula evaluates true ie 3 overlapping bookings trying to stay in only 2 houses over the same dates) but the formula doesnt work further down in the second last line where it should indicate no overlap but it does (ie there is no problem with renters checking into one house on 30-dec-11 and checking out 4-jan-12, then group checking in 4-jan-12 and checking out 7-jan-12 (last line in list), and in other house group checking in 1-jan-12 and checking out 7-jan-12 (2nd last line in list)

can anyone come up with a formula that would achieve what im trying to do???

datein dateout formula result
24-Nov-11 28-Nov-11 TRUE
25-Nov-11 27-Nov-11 TRUE
25-Nov-11 27-Nov-11 TRUE
28-Nov-11 30-Nov-11 FALSE
22-Dec-11 23-Dec-11 FALSE
23-Dec-11 30-Dec-11 FALSE
23-Dec-11 30-Dec-11 FALSE
30-Dec-11 04-Jan-12 FALSE
01-Jan-12 08-Jan-12 TRUE
04-Jan-12 07-Jan-12 FALSE