Identify Overlapping Times with Excel’s SUMPRODUCT & Conditional Formatting

When people have to list their start times, end times and their tasks, how can we tell if any of the times overlap?

One time I solved this by writing a 25-level nested IF statement to compare each of 25 rows of times against each other. It worked. It was ugly, but it worked.

In this video I show a much simpler solution using SUMPRODUCT to flag rows that have overlapping times. In the background, SUMPRODUCT is an array function that does the comparisons instead of a bunch of IF statements in a messy formula that’s hard to build and troubleshoot.

I take it an extra step and apply conditional formatting to automatically highlight rows that have time overlaps.

#SUMPRODUCT #TimeOverlaps #ConditionalFormatting

For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
https://www.linkedin.com/learning/instructors/oz-du-soleil?trk=insiders_28299411_learning
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2