1

I want Excel to check whether two time intervals intersects or not.

data

For example, I3-J3 and I5-J5 intersects.

How can I make Excel to show this intersection in another cell?

Jordan
  • 4,424
  • 2
  • 18
  • 32
Yigit Basbug
  • 13
  • 3
  • What do you mean by "intersection" ? – dot.Py Feb 22 '17 at 13:25
  • 1
    15:15 - 17:05 and 14:09 - 16:14. I mean both intervals includes 16:00. – Yigit Basbug Feb 22 '17 at 13:30
  • 1
    What is the desired output? Do you want to highlight both entries in row 3 and 5 (possibly using conditional formatting)? Or do you want in column `K` a note stating that these two entries interfere with one another? Maybe you want row 5 only to be highlighted (as the first dupe)? Maybe you can add a screenshot of the desired outcome to the post? Are you OK to use VBA for this or do you wish to accomplish this using formulas? – Ralph Feb 22 '17 at 13:43
  • I want in column K a note stating that these two entries interfere with one another. I prefer a formula. – Yigit Basbug Feb 22 '17 at 13:50

2 Answers2

2

The following formula will reveal any interference for the last row. Just copy this formula into cell K3 and copy it down:

=IF(OR(AND(OR(I3>(I$2:I2)),OR(I3<(J$2:J2))),AND(OR(I3>(I$2:I2)),OR(I3<(J$2:J2)))),"interference","OK")

Note that this is an array formula which will have to be entered using Ctrl+ Shift + Enter. For more information on array formulas you might want to read the following article: https://support.office.com/en-us/article/Guidelines-and-examples-of-array-formulas-7D94A64E-3FF3-4686-9372-ECFD5CAA57C7

enter image description here

If you want to show interference for both rows then you'd have to expand upon I$2:I2 and J$2:J2 to include the entire list. So, this might be (for example) I$2:I$500 and J$2:J$500 respectively. Yet, you cannot include the row itself. Otherwise, you'd always get interference because the formula would check against itself. So, you'd have to generate individual formulas for each row and you cannot enter a generic formula and copy it down.

I am not sure how to show (with formulas only) the interfering entry. This is mostly due to the fact that there might be more than one interfering entry which would then have to be listed and separated by ,. I don't think that can be done with formulas only.

Note: the above solution is based on the basic principle that all dates and times in Excel are (essentially) just numbers formatted as dates or time. For more information you might want to read the following: Change date format using substitute or replace So, the above formula is just checking if the date in column I is between any prior date in column I and column J. If the date in column I is > and prior date in column I and also < compared to the date in column J then this is an interference. The same has to be checked for the date in column K and then both have to be combined with an OR. That's the entire formula above.

Update:

Based on the comment provided by @Gordon the formula can be improved with the COUNTIFS function. Just enter the following formula into cell C2 and copy it down:

=SUM(COUNTIFS(A:A,"<"&A2,B:B,">"&A2),COUNTIFS(A:A,"<"&B2,B:B,">"&B2))

Any number greater than 0 means that there is at least one interference. With this formula it is now counting / showing multiple interferences as you can see in the following screenshot:

enter image description here

Note, that the improved formula does not require anymore for you to know the range of the table. Instead you can search the entire column A:A and B:B for interferences.

Community
  • 1
  • 1
Ralph
  • 9,284
  • 4
  • 32
  • 42
  • I like your answer. It can also be done without an array formula if you use countifs function. – Gordon Feb 22 '17 at 14:51
  • @Gordon Thanks. Great catch. I updated the answer. Is this what you had in mind? – Ralph Feb 22 '17 at 20:14
  • 1
    That's what I was thinking would be an answer but I was looking at the question on my phone and didn't have time to respond properly so thanks for doing the work. It's interesting because I've used array formulas with index functions etc. but I'd never thought about them being used with just an IF statement so you learn something new every day on SO. I don't know which solution is less taxing for Excel. – Gordon Feb 22 '17 at 21:49
0

I'd like to improve formula that was provided by @Ralph. That formula considers only two intersection situations - when time intervals overlap. But there are two more situations of intersection:

  • when interval X started before interval Y and finished after interval Y finished (big interval)
  • when interval X started after interval Y and finished before interval Y finished (small interval)

So the final formula for me looks like this:

=SUM(COUNTIFS(A:A, "<"&A2, B:B, ">"&A2), COUNTIFS(A:A, "<"&B2, B:B, ">"&B2), COUNTIFS(A:A, ">"&A2, B:B, "<"&B2), COUNTIFS(A:A, "<"&A2, B:B, ">"&B2))
rosen_krantz
  • 1
  • 1