Overlapping schedule conditional formatting issue

Hi people,

I am making a schedule for 8 measurement stations (file is attached for convenience). What I'm trying to do is to make sure that the time from each measurement station does not overlap. In order for that to work, I need the conditional formatting function. The applied range for conditional formatting would be the same as every other rule, I presume (=$Y$3:$BDH$12).

The interesting columns are from E to X, which contains start time/date and end time/date (Gamry). If any of the 10 Gamry start time overlaps with eachother, I want those cells highlighted with red. I am not sure what else I can explain. Once you see the file, I am sure you will understand.

Thank you! Best regards
FNasync_tool_final_draft_FNa.xlsx

Some cells manually filled to show which cells should be highlighted when there is an overlap would hve helped.

But...

Maybe:

=AND(D5<>"",D5<C5)

applied to D5:X12. This highlights any start/end date that is EARLIER than any one to the left of it.

Perhaps this will help.
1. Populate Y5:BDH12 using:

Formula:

=IF($A5<>"",IFERROR(AGGREGATE(15,6,(COLUMN($E5:$W5)-COLUMN($D5))/((ISNUMBER(SEARCH("end",$F$4:$X$4)))*($F5:$X5>Y$2+Y$3))/((ISNUMBER(SEARCH("start",$E$4:$W$4)))*($E5:$W5<Z$2+Z$3)),1),""),"")

2. Populate Y18:BDH18 using (Note that you may move and or hide this row for aesthetic purposes):

Formula:

=COUNT(Y5:Y12)

3. Replace all dark blue formatting rules with: =AND(Y5<>"",Y5>=1) > font and fill dark blue
4. Use the following as the red formatting rule: =AND(Y5<>"",Y5>=1,Y$18>1)
Note that both rules are applied to the range Y5:BDH12
Let us know if you have any questions.

IFERROR(AGGREGATE(15,6,(COLUMN($E5:$W5)-COLUMN($D5))/((ISNUMBER(SEARCH("end",$F$4:$X$4)))*($F5:$X5>Y$2+Y$3))/((ISNUMBER(SEARCH("start",$E$4:$W$4)))*($E5:$W5<Z$2+Z$3)),1),"")
AGGREGATE(15,6,... 15 is the function number for SMALL and 6 is the option for ignore errors.
(COLUMN($E5:$W5)-COLUMN($D5)) will produce an array from 1:19.
((ISNUMBER(SEARCH("end",$F$4:$X$4)))*($F5:$X5>Y$2+Y$3)) will produce an array of 1's and 0's where the column header contains the word "end" AND the date/times in columns F:X are greater than the date and time in rows 2:3 of the column containing the formula.
((ISNUMBER(SEARCH("start",$E$4:$W$4)))*($E5:$W5<Z$2+Z$3)) will produce an array of 1's and 0's where the column header contains the word "start" AND the date/times in columns E:W are less than the date and time in rows 2:3 of the column to the right of the one containing the formula.
The division symbol, "/", is used so that the 0's will produce #DIV0! errors in the corresponding positions of the original array of 1:19 which will be ignored by the option, "6" and the 1's will leave the corresponding numbers in the original array unchanged.
The ,1) near the right end of the formula will select only the smallest number that is left.
The AGGREGATE formula is wrapped in IFERROR..."" so that if no numbers are left the cell displays a blank.
I suggest selecting one of the dark blue or red cells and utilizing the Evaluate Formula feature to get a better idea of how the formula operates.
Let us know if you have any questions.

The following light blue conditional formatting rule should do what you want for the :

Formula:

=AND($A5<>"",$D5>Y$2+Y$3-30/1440,$C5<Z$2+Z$3)

The following modification to the first formula in post #4 should what you want for the dark blue and red:

Formula:

=IF($A5<>"",IFERROR(AGGREGATE(15,6,(COLUMN($E5:$W5)-COLUMN($D5))/((ISNUMBER(SEARCH("end",$F$4:$X$4)))*($F5:$X5>Y$2+Y$3-30/1440))/((ISNUMBER(SEARCH("start",$E$4:$W$4)))*($E5:$W5<Z$2+Z$3)),1),""),"")

Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.