Can IF(INDEX(MATCH())) return true for multiple rows?

Colin B

I'm trying to develop a formula for the Booked column that will return true if the value in Date/Time is found in another sheet. That much I can do, but my problem is that the timeslots are 4 hours long so I need it to return true for the three following rows as well.

For example, if the first timeslot (9:00-1:00) in the screenshot is booked, I need the 10:00-2:00, 11:00-3:00, and 12:00-4:00 slots to show as booked too. But if the 1:00-5:00 slot is booked, it should not return true for the three slots below as they are on the following day.

Hopefully my requirements are clear. Any answers/links to similar questions would be greatly appreciated!

Paul Newcome

What is your current formula and what does the sheet you are referencing look like?

Eric Law

@Colin B

This is an interesting question, there are a few things that you will need to show first. What are you trying to match on both sheets? How does the other sheet interact? Are they selecting those time slots?

Anyhow, with all that being said, I have a rough solution to this, rough since I don't know anything about your other sheet. Here are some of the ideas that I have for you to do this.

1) Create a helper column with the start time of the 4 hour block (this is to create an integer that you can compare), using 24 hour notation for PM, ie 1pm = 13

2) You will need a create another helper column with a multiselect with the numbers

3) You will need to use a change cell to put numbers in there, for instance, if the time is 7am, then you would change the helper time to have 7, 8, 9, 10

4) The formula you will use will be =IF(HAS(Date:Date, [Book Date]@row), IF(CONTAINS([Book Hour]@row, [Help Hours]:[Help Hours]),1,0),0)

Don't know if that's what you need, as again I'd need to see what your other sheet looks like and what you're trying to do. I know that step 3 is a lot of work, another way you can do that is build a helper sheet with it and vlookup to that so you don't have to build change cells. Lots of different tools at your disposal, hope this helps.

Colin B

Hi Paul and Eric,

Thanks for your replies. I was wrong about needing an index(match()), here is the formula I currently have:

=IF(CONTAINS([Date/Time]@row, {Appointment - Timeslot}), 1)

Screenshot of the sheet I'm referencing is below. Here users are booking appointments through a form where they enter their address and using form logic the dropdown column for that address is shown with the available timeslots (cropped these columns out since they contain real addresses).

I'm using Data Shuttle to automatically update the dropdown options as appointments are booked, hence the Booked column in the first sheet.

Hope this is clear enough. Eric's solution sounds like it may work. I'll give it a shot and check back here Monday.

Paul Newcome

We should be able to leverage the CONTAINS function so long as we isolate the date portion from the [Date/Time]@row reference.

=IF(CONTAINS(LEFT([Date/Time]@row, FIND(",", [Date/Time]@row) - 1), {Appointment - Timeslot}), 1)

Colin B

@Eric Law I gave your solution a shot and it got me pretty close for not knowing the whole picture. I made some tweaks to it and it looks like it should be working, so I believe the issue is now with my formula.

=IF(HAS({Appointment - Timeslot}, [Date/Time]@row), IF(CONTAINS([Last Hour]@row, [Hours Helper]:[Hours Helper]), 1, 0), 0)

In this screenshot, rows 2-4 should also have Booked checked since the Last Hour in row 1 is found in the Hours Helper of rows 2-4.

{Appointment - Timeslot} is referencing the Selected Timeslot column in the screenshot from my last comment.

Paul Newcome

@Colin B Did you give mine a try? If it does work, it helps cut out helper columns and is a more simplified solution overall (if it does in fact work).

Colin B

@Paul Newcome I hadn't until now and it does partially work, however it's checking all timeslots for a given day, rather than just the ones that conflict with the selected timeslot.

Paul Newcome

My apologies. I misunderstood your original post. I thought that was the goal and didn't realize it was just for the overlap. Sorry about that.

Will it always be 4 hour slots, will the first one always start at 9am, and will the last one always end at 5pm?

Colin B

@Paul Newcome No worries, I appreciate the help. Yes to all three of your questions for now, but there is a chance I may have to adjust them in the future so having that option would be nice.

Paul Newcome

Ok. and for the future (potential) updates... Is it possible you could have a start time of 7:00am and an end time of 7:00 pm (even on a different entry)? If so, would you specify am/pm? What are the variables that you would be changing?

Colin B

That would would be pretty unlikely, as the clinics that are scheduling the appointments typically don't open until 8am and we are making every effort to book the appointments during working hours.

However, it is likely that we have one entry that ends at 1pm and another that starts at 1pm.

The duration will never be longer than 4 hours, but may be shortened it to 3, thus changing the available timeslots. Other changing variables would be adding a slot that starts at 8am, ones that start on the half hour.

Paul Newcome

Ok. So if we don't have to worry about having a morning time slot and an evening time slot having the same hour, then that does make life more simple (but only slightly). Would it be safe to assume that any hour less than 8 (or equal to 12) is considered PM and any hour greater than or equal to 8 (but less than 12) is AM?

Paul Newcome

The basic idea is to create a start time column and an end time column. Then we can use formulas to strip out the times from the strings and output numerical values (even accommodating a non-"00" minute). Once we output the numerical values for start and end times, we can leverage those in a typical overlap type of solution where we use a basic IF/COUNTIFS combo.

The variables I have been asking about though will impact the formulas used to convert the time text strings into number values though which is why I haven't provided the formulas yet (except for the wrong one earlier haha).

Colin B

I believe that would be a safe assumption. Could we not use 24hr notation when converting the time strings to numerical values? e.g. the 1:00-5:00 slot would have a start time of 13 and end time of 17. Not sure if that fits with the solution you're thinking of but it would avoid having to differentiate am/pm.

As long as I have a good grasp on what exactly the formulas are doing I'm pretty confident I could make adjustments as needed in the future.

Paul Newcome

Having 24 hour times in your strings would definitely make things much more simple. Is that an option, or are people going to push back on that and insist on the 12 hour format in the strings?