I have a large data set that looks like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]COURSE
[/TD]
[TD]START TIME
[/TD]
[TD]END TIME
[/TD]
[TD]Conflict[/TD]
[/TR]
[TR]
[TD]Module 2[/TD]
[TD]28/01/2019 16:00[/TD]
[TD]28/01/2019 18:00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Module 5[/TD]
[TD]13/02/2019 09:00[/TD]
[TD]13/02/2019 12:00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Module 6[/TD]
[TD]10/10/2019 09:00[/TD]
[TD]10/10/2019 17:00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Module 10[/TD]
[TD]28/01/2019 16:00[/TD]
[TD]28/01/2019 17:00[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
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>
[TABLE="width: 500"]
<tbody>[TR]
[TD]COURSE
[/TD]
[TD]START TIME
[/TD]
[TD]END TIME
[/TD]
[TD]Conflict[/TD]
[/TR]
[TR]
[TD]Module 2[/TD]
[TD]28/01/2019 16:00[/TD]
[TD]28/01/2019 18:00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]Module 5[/TD]
[TD]13/02/2019 09:00[/TD]
[TD]13/02/2019 12:00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Module 6[/TD]
[TD]10/10/2019 09:00[/TD]
[TD]10/10/2019 17:00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]Module 10[/TD]
[TD]28/01/2019 16:00[/TD]
[TD]28/01/2019 17:00[/TD]
[TD]TRUE[/TD]
[/TR]
</tbody>[/TABLE]
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>