Automatically highlighting conflicting/overlapping times on same dates

M

monty12321

New Member
Joined
Aug 27, 2019
Messages
6
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:
[TABLE="width: 376"]
<tbody>[TR]
[TD]Course Type
[/TD]
[TD]Date
[/TD]
[TD]Start and End Time
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]08/12/2019
[/TD]
[TD]09:00-13:00
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]08/12/2019
[/TD]
[TD]09:00-14:00
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]15/08/2019
[/TD]
[TD]09:00-16:00
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]16/08/2019
[/TD]
[TD]09:00-17:00
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]17/08/2019
[/TD]
[TD]09:00-17:00
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]18/08/2019
[/TD]
[TD]09:00-18:00
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]18/08/2020
[/TD]
[TD]15:00-18:00
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]20/08/2019
[/TD]
[TD]09:00-13:00
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]22/08/2019
[/TD]
[TD]08:00-15:00
[/TD]
[/TR]
[TR]
[TD]Training
[/TD]
[TD]22/08/2019
[/TD]
[TD]08:00-11:00
[/TD]
[/TR]
[TR]
[TD]Workshop
[/TD]
[TD]08/12/2019
[/TD]
[TD]09:00-13:00
[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Course Type[/TD]
[TD]Date[/TD]
[TD]Start and End Time[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Clash[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]08/12/2019[/TD]
[TD]09:00-13:00[/TD]
[TD]=TIMEVALUE(LEFT(C2,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C2,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]08/12/2019[/TD]
[TD]09:00-14:00[/TD]
[TD]=TIMEVALUE(LEFT(C3,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C3,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]15/08/2019[/TD]
[TD]09:00-16:00[/TD]
[TD]=TIMEVALUE(LEFT(C4,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C4,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]16/08/2019[/TD]
[TD]09:00-17:00[/TD]
[TD]=TIMEVALUE(LEFT(C5,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C5,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]17/08/2019[/TD]
[TD]09:00-17:00[/TD]
[TD]=TIMEVALUE(LEFT(C6,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C6,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]18/08/2019[/TD]
[TD]09:00-18:00[/TD]
[TD]=TIMEVALUE(LEFT(C7,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C7,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]18/08/2019[/TD]
[TD]15:00-18:00[/TD]
[TD]=TIMEVALUE(LEFT(C8,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C8,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]20/08/2019[/TD]
[TD]09:00-13:00[/TD]
[TD]=TIMEVALUE(LEFT(C9,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C9,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]22/08/2019[/TD]
[TD]08:00-15:00[/TD]
[TD]=TIMEVALUE(LEFT(C10,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C10,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Training[/TD]
[TD]22/08/2019[/TD]
[TD]08:00-11:00[/TD]
[TD]=TIMEVALUE(LEFT(C11,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C11,5))[/TD]
[TD]=((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[/TD]
[/TR]
[TR]
[TD]Workshop[/TD]
[TD]08/12/2019[/TD]
[TD]09:00-13:00[/TD]
[TD]=TIMEVALUE(LEFT(C12,5))[/TD]
[TD]=TIMEVALUE(RIGHT(C12,5))[/TD]
[TD]=((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[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Is this a typo?
[TABLE="class: cms_table, width: 376"]
<tbody>[TR]
[TD]Workshop
[/TD]
[TD]18/08/2020
[/TD]
[TD]15:00-18:00[/TD]
[/TR]
</tbody>[/TABLE]

Shouldn't it be?
[TABLE="class: cms_table, width: 376"]
<tbody>[TR]
[TD]Workshop
[/TD]
[TD]18/08/2019
[/TD]
[TD]15:00-18:00
[/TD]
[/TR]
</tbody>[/TABLE]

M.
 
Upvote 0
Hey Gerald,

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

Thanks,

Monty
 
Upvote 0
monty12321 said:
Marcelo,

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

Maybe...

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A​
[/TD]
[TD="bgcolor: #DCE6F1"]
B​
[/TD]
[TD="bgcolor: #DCE6F1"]
C​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1​
[/TD]
[TD]
Course Type​
[/TD]
[TD]
Date​
[/TD]
[TD]
Start and End Time​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
08/12/2019​
[/TD]
[TD]
09:00-13:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
08/12/2019​
[/TD]
[TD]
09:00-14:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4​
[/TD]
[TD]
Training​
[/TD]
[TD]
15/08/2019​
[/TD]
[TD]
09:00-16:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
16/08/2019​
[/TD]
[TD]
09:00-17:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6​
[/TD]
[TD]
Training​
[/TD]
[TD]
17/08/2019​
[/TD]
[TD]
09:00-17:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7​
[/TD]
[TD]
Training​
[/TD]
[TD]
18/08/2019​
[/TD]
[TD]
09:00-18:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
8​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
18/08/2019​
[/TD]
[TD]
15:00-18:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
9​
[/TD]
[TD]
Training​
[/TD]
[TD]
20/08/2019​
[/TD]
[TD]
09:00-13:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
10​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
22/08/2019​
[/TD]
[TD]
08:00-15:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
11​
[/TD]
[TD]
Training​
[/TD]
[TD]
22/08/2019​
[/TD]
[TD]
08:00-11:00​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
12​
[/TD]
[TD]
Workshop​
[/TD]
[TD]
08/12/2019​
[/TD]
[TD]
09:00-13:00​
[/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Last edited:
Upvote 0
monty12321 said:
Hey Gerald,

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

I hadn't seen that...

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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Course Type​
[/td][td]
Date​
[/td][td]
Start​
[/td][td]
End​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
Workshop​
[/td][td]
08/12/2019​
[/td][td]
09:00​
[/td][td]
13:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
Workshop​
[/td][td]
08/12/2019​
[/td][td]
09:00​
[/td][td]
14:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
Training​
[/td][td]
15/08/2019​
[/td][td]
09:00​
[/td][td]
16:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
Workshop​
[/td][td]
16/08/2019​
[/td][td]
09:00​
[/td][td]
17:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
Training​
[/td][td]
17/08/2019​
[/td][td]
09:00​
[/td][td]
17:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
Training​
[/td][td]
18/08/2019​
[/td][td]
09:00​
[/td][td]
18:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
Workshop​
[/td][td]
18/08/2019​
[/td][td]
15:00​
[/td][td]
18:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
Training​
[/td][td]
20/08/2019​
[/td][td]
09:00​
[/td][td]
13:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
Workshop​
[/td][td]
22/08/2019​
[/td][td]
08:00​
[/td][td]
15:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
Training​
[/td][td]
22/08/2019​
[/td][td]
08:00​
[/td][td]
11:00​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
Workshop​
[/td][td]
08/12/2019​
[/td][td]
09:00​
[/td][td]
13:00​
[/td][/tr]
[/table]


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.
 
Last edited:
Upvote 0
All,


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


Regards,

Monty
 
Upvote 0
You must log in or register to reply here.

Similar threads

welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
95
welshgasman
welshgasman
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
283
shift-del
shift-del
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw
V
  • Question Question
Hours functions
Replies
4
Views
569
jkpieterse
jkpieterse
AOB
  • Question Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
718
AOB
AOB

Forum statistics

Threads
1,224,747
Messages
6,180,719
Members
452,995
Latest member
isldboy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top