Automatically highlighting conflicting/overlapping times on same dates

[monty12321]

Good afternoon,

I was wondering if you could use your help on excel. I have a excel spreadsheet which automatically imports room bookings data from the Microsoft word forms. However, sometimes we get clashes with requests overlapping each other. I would like this to be highlighted automatically instead of manually checking it. Below is the sample:

Course Type	Date	Start and End Time
Workshop	08/12/2019	09:00-13:00
Workshop	08/12/2019	09:00-14:00
Training	15/08/2019	09:00-16:00
Workshop	16/08/2019	09:00-17:00
Training	17/08/2019	09:00-17:00
Training	18/08/2019	09:00-18:00
Workshop	18/08/2020	15:00-18:00
Training	20/08/2019	09:00-13:00
Workshop	22/08/2019	08:00-15:00
Training	22/08/2019	08:00-11:00
Workshop	08/12/2019	09:00-13:00

<tbody>
</tbody>

As you can see there are several overlaps/conflicts, but I must highlight this manually. Hence, is there a way to get excel to do this automatically. I understand this could be done by formula “Sumproduct” but, I was unable to get it to work thus I looked around and stumbled upon this https://www.mrexcel.com/forum/excel-questions/429758-find-time-overlaps.html and found the other formula(s) but i am still unable to get it working. The formula still gives the "False" value despite the overlaps/conflicts.

Therefore, any help would be much appreciated.

Thanks

Monty

 

[Gerald Higgins]

Are your start time and end time in the same column, or two different columns ?

 

[trunten]

I hate working out time clashes!
Anyway, not sure if this is bulletproof but I thought I'd give it a go. What I would suggest is adding a few helper columns to the sheet so you have something like this:

Course Type	Date	Start and End Time	Start	End	Clash
Workshop	08/12/2019	09:00-13:00	=TIMEVALUE(LEFT(C2,5))	=TIMEVALUE(RIGHT(C2,5))	=((COUNTIFS($D$2:$D$12,"<="&$D2,$E$2:$E$12,">="&$D2,$B$2:$B$12,$B2)-1)+COUNTIFS($D$2:$D$12,">"&$D2,$D$2:$D$12,"<"&$E2,$B$2:$B$12,$B2))>0
Workshop	08/12/2019	09:00-14:00	=TIMEVALUE(LEFT(C3,5))	=TIMEVALUE(RIGHT(C3,5))	=((COUNTIFS($D$2:$D$12,"<="&$D3,$E$2:$E$12,">="&$D3,$B$2:$B$12,$B3)-1)+COUNTIFS($D$2:$D$12,">"&$D3,$D$2:$D$12,"<"&$E3,$B$2:$B$12,$B3))>0
Training	15/08/2019	09:00-16:00	=TIMEVALUE(LEFT(C4,5))	=TIMEVALUE(RIGHT(C4,5))	=((COUNTIFS($D$2:$D$12,"<="&$D4,$E$2:$E$12,">="&$D4,$B$2:$B$12,$B4)-1)+COUNTIFS($D$2:$D$12,">"&$D4,$D$2:$D$12,"<"&$E4,$B$2:$B$12,$B4))>0
Workshop	16/08/2019	09:00-17:00	=TIMEVALUE(LEFT(C5,5))	=TIMEVALUE(RIGHT(C5,5))	=((COUNTIFS($D$2:$D$12,"<="&$D5,$E$2:$E$12,">="&$D5,$B$2:$B$12,$B5)-1)+COUNTIFS($D$2:$D$12,">"&$D5,$D$2:$D$12,"<"&$E5,$B$2:$B$12,$B5))>0
Training	17/08/2019	09:00-17:00	=TIMEVALUE(LEFT(C6,5))	=TIMEVALUE(RIGHT(C6,5))	=((COUNTIFS($D$2:$D$12,"<="&$D6,$E$2:$E$12,">="&$D6,$B$2:$B$12,$B6)-1)+COUNTIFS($D$2:$D$12,">"&$D6,$D$2:$D$12,"<"&$E6,$B$2:$B$12,$B6))>0
Training	18/08/2019	09:00-18:00	=TIMEVALUE(LEFT(C7,5))	=TIMEVALUE(RIGHT(C7,5))	=((COUNTIFS($D$2:$D$12,"<="&$D6,$E$2:$E$12,">="&$D6,$B$2:$B$12,$B6)-1)+COUNTIFS($D$2:$D$12,">"&$D6,$D$2:$D$12,"<"&$E6,$B$2:$B$12,$B6))>0
Workshop	18/08/2019	15:00-18:00	=TIMEVALUE(LEFT(C8,5))	=TIMEVALUE(RIGHT(C8,5))	=((COUNTIFS($D$2:$D$12,"<="&$D8,$E$2:$E$12,">="&$D8,$B$2:$B$12,$B8)-1)+COUNTIFS($D$2:$D$12,">"&$D8,$D$2:$D$12,"<"&$E8,$B$2:$B$12,$B8))>0
Training	20/08/2019	09:00-13:00	=TIMEVALUE(LEFT(C9,5))	=TIMEVALUE(RIGHT(C9,5))	=((COUNTIFS($D$2:$D$12,"<="&$D9,$E$2:$E$12,">="&$D9,$B$2:$B$12,$B9)-1)+COUNTIFS($D$2:$D$12,">"&$D9,$D$2:$D$12,"<"&$E9,$B$2:$B$12,$B9))>0
Workshop	22/08/2019	08:00-15:00	=TIMEVALUE(LEFT(C10,5))	=TIMEVALUE(RIGHT(C10,5))	=((COUNTIFS($D$2:$D$12,"<="&$D9,$E$2:$E$12,">="&$D9,$B$2:$B$12,$B9)-1)+COUNTIFS($D$2:$D$12,">"&$D9,$D$2:$D$12,"<"&$E9,$B$2:$B$12,$B9))>0
Training	22/08/2019	08:00-11:00	=TIMEVALUE(LEFT(C11,5))	=TIMEVALUE(RIGHT(C11,5))	=((COUNTIFS($D$2:$D$12,"<="&$D11,$E$2:$E$12,">="&$D11,$B$2:$B$12,$B11)-1)+COUNTIFS($D$2:$D$12,">"&$D11,$D$2:$D$12,"<"&$E11,$B$2:$B$12,$B11))>0
Workshop	08/12/2019	09:00-13:00	=TIMEVALUE(LEFT(C12,5))	=TIMEVALUE(RIGHT(C12,5))	=((COUNTIFS($D$2:$D$12,"<="&$D12,$E$2:$E$12,">="&$D12,$B$2:$B$12,$B12)-1)+COUNTIFS($D$2:$D$12,">"&$D12,$D$2:$D$12,"<"&$E12,$B$2:$B$12,$B12))>0

<tbody>
</tbody>

 

[Marcelo Branco]

Is this a typo?

Workshop

18/08/2020

15:00-18:00

<tbody>
</tbody>

Shouldn't it be?

Workshop

18/08/2019

15:00-18:00

<tbody>
</tbody>

M.

 

[monty12321]

Hey Gerald,

Currently, they are in indeed in a same column but they can be two different column if that helps.

Thanks,

Monty

 

[monty12321]

Marcelo,

Indeed, that my fault. It should be 18/08/2019. I'll see if i can edit the thread.

Regards,

M

 

[Marcelo Branco]

Marcelo,

Indeed, that my fault. It should be 18/08/2019. I'll see if i can edit the thread.

Maybe...

	A​	B​	C​
1​	Course Type​	Date​	Start and End Time​
2​	Workshop​	08/12/2019​	09:00-13:00​
3​	Workshop​	08/12/2019​	09:00-14:00​
4​	Training​	15/08/2019​	09:00-16:00​
5​	Workshop​	16/08/2019​	09:00-17:00​
6​	Training​	17/08/2019​	09:00-17:00​
7​	Training​	18/08/2019​	09:00-18:00​
8​	Workshop​	18/08/2019​	15:00-18:00​
9​	Training​	20/08/2019​	09:00-13:00​
10​	Workshop​	22/08/2019​	08:00-15:00​
11​	Training​	22/08/2019​	08:00-11:00​
12​	Workshop​	08/12/2019​	09:00-13:00​

<tbody>
</tbody>

Select A2:C12

Home > Conditional Formatting > New rule > Use a formula to determine which cells to format

Insert this formula
=SUMPRODUCT(--($B$2:$B$12=$B2),--(1-((--"0"&LEFT($C$2:$C$12,5)>=--"0"&RIGHT($C2,5))+(--"0"&LEFT($C2,5)>=--"0"&RIGHT($C$2:$C$12,5)))))>1

pick the format you want (Font red in the example)

Hope this helps

M.

 

[Marcelo Branco]

Hey Gerald,

Currently, they are in indeed in a same column but they can be two different column if that helps.

I hadn't seen that...

Try
Select your data, say A2:D20 (not the headers)

	A​	B​	C​	D​
1​	Course Type​	Date​	Start​	End​
2​	Workshop​	08/12/2019​	09:00​	13:00​
3​	Workshop​	08/12/2019​	09:00​	14:00​
4​	Training​	15/08/2019​	09:00​	16:00​
5​	Workshop​	16/08/2019​	09:00​	17:00​
6​	Training​	17/08/2019​	09:00​	17:00​
7​	Training​	18/08/2019​	09:00​	18:00​
8​	Workshop​	18/08/2019​	15:00​	18:00​
9​	Training​	20/08/2019​	09:00​	13:00​
10​	Workshop​	22/08/2019​	08:00​	15:00​
11​	Training​	22/08/2019​	08:00​	11:00​
12​	Workshop​	08/12/2019​	09:00​	13:00​

Use this formula in Conditional Formatting
=SUMPRODUCT(--($B$2:$B$20=$B2),--($C$2:$C$20<>""),--(1-(($C$2:$C$20>=$D2)+($C2>=$D$2:$D$20))))>1

M.

 

[monty12321]

All,


Apologiesfor the delayed response. Thank you very much for taking time out to help. Iappreciate your help.


Regards,

Monty