CODEWITHSUNDEEP
excelvalidationdateexcel-formula
Mr. J
Mr. J

Reputation: 317

Excel formula to say if a date range is available/unavailable from a list of date ranges?

I'm creating a booking system in Excel and need a formula to tell me if a given date range is available/unavailable from a list of date ranges (bookings).

There's an excellent article on Chandoo.org here about testing to see if two ranges of dates overlap. However the formula provided only works when comparing one set of date ranges to another single set of date ranges. Whereas I require this to work/search through a multitude of date ranges.

image

=IF(OR(y<a,b<x),"Do not overlap","Overlap")

Take the bookings below for example:

*Bookings*
Start Date  End Date
25/01/2019  26/01/2019
29/01/2019  31/01/2019
01/02/2019  03/02/2019

Based on the above, the following dates are available:

21/01/2019  24/01/2019
27/01/2019  28/01/2019

If I fed the formula either of these two sets of dates, I would expect the outcome to read "Available".

Subsequently, the following dates are unavailable:

23/01/2019  26/01/2019
30/01/2019  02/02/2019

...and the formula should read "Unavailable".

As far as I'm aware, the logic around the formula from Chandoo.org does not work when converted to an array of search/compare criteria.

I thought I came close by using an array within two separate =COUNTIF() statements for each comparison y<a or b<x, however the outcomes weren't always correct.

The fact that a simple formula exists comparing two individual sets of date ranges makes me a believe a more complicated formula is derivable which compares a single date range to an array of date ranges.

Any help or guidance is much appreciated. Thanks, Mr J.

Upvotes: 1

Views: 314

Answers (1)

Justyna MK
Justyna MK

Reputation: 3563

I hope this solution will help:

Assuming your actual Bookings are present in columns A&B (I copied your dates into cells A3:B5), and your four potential bookings are in columns D&E (cells D1:E4), you can paste the following formula for the first row:

=IF(COUNTIFS($A$3:$A$5,"<="&E1,$B$3:$B$5,">="&E1)+COUNTIFS($A$3:$A$5,"<="&D1,$B$3:$B$5,">="&D1),"Overlap","Do not overlap")

Drag & copy it to the remaining 3 rows. You should see "Do not overlap" for the first two dates and "Overlap" for the remaining two bookings. Let me know if this is what you were looking for.

Upvotes: 0

Related Questions