Conditional formatting rule to see if any booking dates overlap

[lawrencef]

i rent out a holiday house and i keep track of bookings in an excel spreadsheet.

i have previously checked for overlapping bookings by a conditional format formula using sumproduct

here is my booking list

bookinglist.jpg

i use sumproduct in a conditional formatting formula along the lines of =SUMPRODUCT(($A2<$B$2:$B$29)*($B2>$A$2:$A$29))>1 as per explained in this post .... http://www.get-digital-help.com/2010...ting-in-excel/

i now want to improve the conditional formatting formula to allow me to leave a cancelled booking in the list. if i currently do this (and change the booking status to cxld) then the conditional format gives me a false red indicating there is an overlapping booking. My question is .... how can i adjust the formula to factor in the ability for me to change the status of a booking to cancelled (for example change the status of the 24-dec-15 to 31-dec-15 booking to cxld and not have the conditional format give me a false red indicator) .... but still obviously give me a true double booking red indicator if there was a confirmed (cfmd) double booking at any time. I thought i could add an AND(.... statement to the existing formula and somehow check the status range for the word "cfmd" etc but i couldnt get it working.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[lawrencef]

I think ive partially solved it by appending to the sumproduct formula ..... i ended up with the following conditional formatting formula and seems to work ok for now
=SUMPRODUCT(($A2<$B$2:$B$29)*($B2>$A$2:$A$29)*("cxld"<>$D$2:$D$29))>1 ..... this essentially will only flag the line as a potential overlapping booking if the entry in D column is not listed as a cancelled ("cxld") ....