If dates in range overlap and values in x match return value

[uk_2022]

Further to my last post, I have solved the overlapping dates formula however I am struggling with the next part.

In column P I would like a formula that would return "Available" if;

a) the check in date and check out date do not overlap any others within the range
b) the check in date and check out date do overlap others within the range but the overlapping dates do not require the same piece of equipment that is selected in column N.

or would return return "Not Available" if;

a) the check in date and check out date overlap another booking and require the same piece of equipment that is selected in column N.

For example; (see image)

There is a booking from 26/08/22 until 29/08/22 and another from the 26/08/22 until 02/09/22. They both want to book the "Sunlift Micro Hoist".

There is a third booking from 26/08/22 until 29/08/22 but requires the "Birdie Compact Hoist"

In column P, I have added the formula;

=IF(AND(SUMPRODUCT((I17<$K$9:$K$17)*(K17>$I$9:$I$17))>1,MATCH(N17,$N$9:$N$17,0)),"Check","Available")

but I need to tweak it so that it only returns "Check" is the dates overlap AND the equipment values match.

Can anyone advise?

TIA

 

[jorismoerings]

Hi,

Try inserting the MATCH on equipment into tHE SUMPRODUCT part, like this:

=IF(SUMPRODUCT((I17<$K$9:$K$17)*(K17>$I$9:$I$17)*(N17=$N$9:$N$17))>1,"Check","Available")

 

[uk_2022]

Thank you!

 

[uk_2022]

Sorry, not quite there.

Column N can contain multiple values in a cell therefore I need the formula to search the cells in column N for any matching values.

I have attached an example.

Example: Check in dates overlap and both rows contain "Birdie Compact Hoist" in column N, however row 10 also contains "Aquatec......." in the same cell.

Currently, column P is returning "available" as it is not picking up that "Birdie Compact Hoists" has appeared in both cells.

Can you advise?

TIA

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: if dates overlap and value match enter value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[uk_2022]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: if dates overlap and value match enter value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thanks. I posted my scenario here first yesterday. The other forum post was posted today as I haven't managed to resolve the issue here.

 

[Fluff]

That does not change the fact, that you need to inform both sites that you have cross posted.

 

[uk_2022]

That does not change the fact, that you need to inform both sites that you have cross posted.

Done.