Help with multiple conditions formula

[pete7722]

Hi all,

Im in need of some help with a multiple conditions formula. I have a vehicle booking workbook with a daily booking view. I am trying to break it down into part days but cannot get the conditions to meet.

The formula i am using is:

=IF(AND($B17<>"",$C17<>""),1-SUMPRODUCT(('Car Reservation'!$F$2:$F$1000<=E$15)*('Car Reservation'!$G$2:$G$1000>=E$15)*('Car Reservation'!$H$2:$H$1000=$B17)*('Car Reservation'!$I$2:$I$1000=$C17)*('Car Reservation'!$D$2:$D$1000<=E$16)*('Car Reservation'!$E$2:$E$1000>=E$16)),"")

F = Start Date
G = End Date
H = Car Make
I = Car Registration
D = Start Time
E = End Time

Thank you very much in advance for your help.

Regards,

Pete


I can get the formula to meet the conditions for the beginning time of each day but not to recognize the end time on a different day.

 

[Andrew Poulsom]

Like this?

=IF(AND($B17<>"",$C17<>""),LEFT(TRIM(INDEX('Car Reservation'!$B$2:$B$11,MATCH(1,INDEX((('Car Reservation'!$F$2:$F$11+'Car Reservation'!$D$2:$D$11)<=(E$15+E$16))*(('Car Reservation'!$G$2:$G$11+'Car Reservation'!$E$2:$E$11)>=(E$15+E$16))*('Car Reservation'!$H$2:$H$11=$B17)*('Car Reservation'!$I$2:$I$11=$C17),),FALSE))),5),"")

 

[pete7722]

PERFECT!!!! thank you very much for all of your help you are a superstar!