Evaluate times within a range

Occasional Visitor



I have a table of activities within days with start and end times. There are 18 blocks of 6 activities (rows) per day, sorted by start time. Each activity has a different duration so may start or finish before or after the start or end time of the next activity. The unique Id for each row is a concatenation of the date and the activity number. For each block of activities I want to conditionally format if any of the start or end times are within half an hour (red) or an hour (amber) of any other activity for that day


Is this possible? I started by sorting all the start and end times for each day in a separate sheet. I managed to format if consecutive values were within a hour of each other but ideally I want to loop around the whole block for a day and compare each time to all of the others.


In the dim and distant past when I was coding I would have done a "Do While", is there an equivalent in excel? Do I need to code a macro?

1 Reply


Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?