I have a large data set that looks like this:
<tbody>
</tbody>
What I need to do is check for overlaps in event times so that I can collate a list of incompatible courses. I found the formula below online which appears to be correctly flagging up courses that have time overlaps with other rows in the dataset:
=SUMPRODUCT((B2<enddate)*(c2>=STARTDATE))>1
What I still need to do is identify which courses are overlapping, so ideally my spreadsheet would look like this:
<tbody>
</tbody>
I'm completely stuck on this. Is there any formula you can suggest? I'm also open to the idea of that I have went about this in the wrong way to begin with and there's a much simpler solution to be found.
Thanks so much.</enddate)*(c2>
COURSE | START TIME | END TIME | Conflict |
Module 2 | 28/01/2019 16:00 | 28/01/2019 18:00 | TRUE |
Module 5 | 13/02/2019 09:00 | 13/02/2019 12:00 | FALSE |
Module 6 | 10/10/2019 09:00 | 10/10/2019 17:00 | FALSE |
Module 10 | 28/01/2019 16:00 | 28/01/2019 17:00 | TRUE |
<tbody>
</tbody>
What I need to do is check for overlaps in event times so that I can collate a list of incompatible courses. I found the formula below online which appears to be correctly flagging up courses that have time overlaps with other rows in the dataset:
=SUMPRODUCT((B2<enddate)*(c2>=STARTDATE))>1
What I still need to do is identify which courses are overlapping, so ideally my spreadsheet would look like this:
COURSE | START TIME | END TIME | CONFLICT |
Module 10 | 28/01/2019 16:00 | 28/01/2019 17:00 | Module 2 |
<tbody>
</tbody>
I'm completely stuck on this. Is there any formula you can suggest? I'm also open to the idea of that I have went about this in the wrong way to begin with and there's a much simpler solution to be found.
Thanks so much.</enddate)*(c2>