Excel Formula to Check for Overlaps in Day and Time
I am building an Excel spreadsheet for weekly class schedules. There are 3 columns where people input a schedule in the following format: "[day] [starttime] [endtime]"
Example:
no. | day | start time | end time |
---|---|---|---|
1 | MTh | 7:00 AM | 9:00 AM |
2 | TF | 7:00 AM | 9:00 AM |
3 | M | 8:30 AM | 9:30 AM |
In this example, what I want is for Excel to highlight rows 1 and 3, because they have a time overlap on Monday
I'm looking for a formula that I can also put inside conditional formatting.
So far, I am only able to highlight time overlaps but have not considered the day. I used a SUMPRODUCT formula below:
=SUMPRODUCT((M15<$N$15:$N$26)*(N15>$M$15:$M$26))>1
Where M15 is start time and N15 is the end time. The N column range refers to all the end times and M column range refers to all the start times
As much as possible I'd like to use Excel formulas and avoid any VB scripting. But if it's not possible with only formulas, I'm open to suggestions. Thanks!
Just add the condition of the day column to the formula you provided.
=SUMPRODUCT((C2<$D$2:$D$4)*(D2>=$C$2:$C$4)*(B2=$B$2:$B$4))>1
Update:
Please try the following formulas:
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("M",$B$2:$B$6))*(COUNT(FIND("M",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("T",$B$2:$B$6))*(COUNT(FIND("T",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("W",$B$2:$B$6))*(COUNT(FIND("W",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("H",$B$2:$B$6))*(COUNT(FIND("H",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("F",$B$2:$B$6))*(COUNT(FIND("F",$B$2:$B$6))>1))>0
SUMPRODUCT((D2>=$C$2:$C$6)*(B2=$B$2:$B$6)*ISNUMBER(FIND("S",$B$2:$B$6))*(COUNT(FIND("S",$B$2:$B$6))>1))>0