formula to check overlapping dates where 2 houses are avail for rent

[lawrencef]

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 this forum and from get-digital-help 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

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

 

[xld]

Does this do it?


=SUMPRODUCT(($A8<$B$2:$B$11+1)*($B8>$A$2:$A$11-1))>=3

 

[vaskov17]

I think you need to add a column for the dates only and count the overlapping days for each date. Not sure if you can do it without additional columns.

 

[Luke M]

You need some way of keeping track of the different houses. This formula goes through and flags each entry for a house, and then uses that to flag if an overbooking occurs.

In C2:

=IF(SUMPRODUCT(--(A2<B$1:B1),--(B2>A$1:A1),--("A"=C$1:C1)),IF(SUMPRODUCT(--(A2<B$1:B1),--(B2>A$1:A1),--("B"=C$1:C1)),"overbooked","B"),"A")


Good: It will let you know if over-booking occurs

Bad: Only flags the latter entries that cause problems (3rd entry in example), not all of them. Not sure if this is critical or not.

 

[lawrencef]

thx guys. we actually do manually allocate the bookings to a house ... either "a" or "b". and the datein and dateout are put in separate columns already .... here is a more full booking list ....


given that this keeps track of different houses, would this make the formula easier to create??


datein dateout house myformula

24-Nov-11 28-Nov-11 a TRUE

25-Nov-11 27-Nov-11 b TRUE

25-Nov-11 27-Nov-11 b TRUE

28-Nov-11 30-Nov-11 a FALSE

22-Dec-11 23-Dec-11 b FALSE

23-Dec-11 30-Dec-11 a FALSE

23-Dec-11 30-Dec-11 b FALSE

30-Dec-11 4-Jan-12 a FALSE

1-Jan-12 8-Jan-12 b TRUE

4-Jan-12 7-Jan-12 a FALSE

 

[xld]

Did you try


=SUMPRODUCT(($A2<$B$2:$B$11+1)*($B2>$A$2:$A$11-1))>=3

 

[Luke M]

xld,

Your formula does not work. Flags rows 6:11 even though those are valid dates.


lawrencef,

Having the house used helps a lot. If the formula I gave before is not enough, you can simply use this:

=SUMPRODUCT(($A2<$B$2:$B$11)*($B2>$A$2:$A$11),--($C2=$C$2:$C$11))>1

 

[xld]

Depends upon whether FALSE means it is an invalid booking, or whether TRUE does. If I have it the wrong way around, it can be reveres simply


=SUMPRODUCT(($A2<$B$2:$B$11+1)*($B2>$A$2:$A$11-1))<3


Your formula kicks out row 4 because of the b when house a is empty, manually assigning a/b is pointless, let the formula do it.

 

[lawrencef]

thx v much guys - luke m ... i varied your original formula a bit and it works well. ill have a look at your most recent posting too to see if that gives even a better solution but thx for the information. xld - ill check your solution too and let you know.

 

[Luke M]

Glad it's working.

xld, I apologize, I should have been clearer. I believe the a "correct" formula will flag records 1-3 (or rows 2:4, assuming data starts on row 2). lawrencef's original formula was flagging row 10, which was incorrect. Your formula appears to flag everything except row 5.


I do agree that it would probably be better to let a formula assign the houses. Why do extra work?