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

Colin B

I was thinking we would still use 12hr for the timeslots and just convert to 24hr in the Start Time and End Time column formulas, but if that doesn't work with your solution I'll go up the chain of command to see if there's pushback on using 24hr for the timeslots.

Paul Newcome

Yeah. I had planned on converting to 24 hour times on the back-end, but the formula needed for the conversion can vary depending on those other pieces I was asking about such as the possibility of a 7am and 7pm or certain numbers being considered am vs pm.

The biggest question I still have is...

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?

Colin B

Okay I gotcha. I did get confirmation that 24hr notation would be okay to use for the timeslots, no pushback on that.

But to answer your question, yes that is a safe assumption.

Paul Newcome

Ok. In that case we can do a Start Time column with this formula:

=VALUE(LEFT(Timeslot@row, FIND(":", Timeslot@row) - 1)) + (VALUE(MID(Timeslot@row, FIND(":", Timeslot@row) + 1, 2)) / 60)

And then this would go in an End Time column:

=VALUE(MID(Timeslot@row, FIND("-", TImeslot@row) + 1, FIND(":", Timeslot@row, 4) - (FIND("-", TImeslot@row) + 1))) + (VALUE(RIGHT(Timeslot@row, 2)) / 60)

Now you should have a number in both columns that is the hour and then a decimal to represent the numbers. You will need these on both sheets.

From there we can use a basic overlap solution where we use a COUNTIFS to see how many rows have the same date, an end time less than the start time and a start time greater than the end time. If that count is greater than 0, there is an overlap.

=IF(COUNTIFS({Date Column}, @cell = Date@row, {Start Helper}, @cell< [End Helper]@row, {End Helper}, @cell> [Start Helper]@row)> 0, 1)

Colin B

Hey Paul, I gave your solution a shot and got the Start Helper and End Helper working, but the COUNTIFS formula isn't quite doing what it should.

I may be wrong but I believe the issue is that it's looking for when the Start/End for a given row from the Availability sheet overlaps in the Start/End columns of the Appointment sheet, but I need it to do the reverse and take the Start/End in the Appointment sheet and find overlaps in the Start/End columns in the Availability sheet.

I hope this makes sense, I tried to reverse it myself but couldn't get the arguments right. Updated screenshots are below, third one is how it should look with the appointment in the first one.

Paul Newcome

My above was based on the assumption you would be using 24 hour time notations in your Timeslot column.

Colin B

Ah, that's my mistake. I updated the timeslots to 24hr on both sheets but unfortunately still no dice.

Paul Newcome

What is the formula in the Appointment Date column?

Colin B

=DATE(VALUE(MID([Selected Timeslot]@row, 7, 2)), VALUE(LEFT([Selected Timeslot]@row, 2)), VALUE(MID([Selected Timeslot]@row, 4, 2)))

Paul Newcome

That should be outputting a date value which means it should be working. I am actually also working on another thread where another user has date values not being recognized for some reason.

What do you get from this:

=COUNTIFS({Appointment Date}, ISDATE(@cell))

Colin B

That formula returned 1 for me. Both Appointment Date and Date are date type columns restricted to dates only. I wonder if that's just a coincidence or evidence of a larger problem.

I'm going to try deleting & recreating the cross-sheet references, that's solved some issues for me in the past.

Edit: It did not work.

Paul Newcome

@Colin B I'm not sure what is going on. The other user having issues is also still having issues.

@Genevieve P. Have there been any reports that you are aware of indicating issues referencing date fields in COUNTIFS. I have this one and another one. The other one we even did a basic

=COUNTIFS({Range}, ISDATE(@cell))

and go the expected count but keep running into issues when we try to evaluate any further than that.

Genevieve P.

Thanks for the tag, @Paul Newcome!

@Colin B it looks like your formula is doing what it should, especially as the COUNTIF troubleshooting test returned the correct result.

I see that you have 9 and 13 as the values you're looking for, but in your formula you're only looking for greater than 9 and less than 13, with no = sign. That means it won't find a match because 9 is not greater than 9. Does that make sense?

Try adding in an = sign with your < and >

=IF(COUNTIFS({Date Column}, @cell = Date@row, {Start Helper}, @cell<= [End Helper]@row, {End Helper}, @cell>= [Start Helper]@row)> 0, 1)

Cheers,

Genevieve

Genevieve P.

@Paul Newcome other thread? 👀 I'll see if I can find it.

No reports of Dates and COUNTIFS returning unexpected results so I'd like to see if there's another possible cause.

Paul Newcome

@Genevieve P. We are looking for overlap on this one. If one ends at 9 and the other starts at 9, we aren't worried about that (thus the lack on an = symbol).

Based on the logic in the formula, the top four rows should be checked. We have the same date for all 4 rows then in row one, 9 is less than 13, and 13 is greater than 9. Row two: 9 is less than 14, and 13 is greater than 10. So on and so forth.

The 5th row shouldn't be checked because even though it is the same date, because 13 is not less than 13 (that timeslot is open).

Or do I have my logic twisted up a bit from staring at it for too long?