If date in 'Col E' falls between Date range in 'Col C' and 'Col D', Turn red

[Kikaiki]

How do I put the above into a working formula in 365?

'C' is Start of ETA
'D' is End of ETA
'E' is arrival time

I need anything that isn't within the date range in C & D to flag red

For example, Row 2 is 30 minutes out so is not compliant within given ETA
Row 4 is net day so also not compliant

I need to be able to flag these without having to go through the whole Doc to specifically dig them out

 

[etaf]

=NOT(AND( $C2 <= $E2, $C2 >= $D2))

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C2:C100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=NOT(AND( $C2 <= $E2, $C2 >= $D2))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book2
	A	B	C	D	E	F
1
2			1/1/24 0:00	2/1/24 0:00	3/1/24 0:00	TRUE
3			1/11/24 0:00	2/2/24 0:00	3/2/24 0:00	TRUE
4			11/29/24 8:00	11/29/24 18:00	3/3/24 13:00	TRUE
5			1/21/24 0:00	2/4/24 0:00	3/4/24 0:00	TRUE
6			12/9/24 8:00	2/5/24 0:00	3/5/24 0:00	TRUE
7			1/31/24 0:00	1/6/24 0:00	4/6/24 0:00	FALSE
8			12/19/24 8:00	2/7/24 0:00	3/7/24 0:00	TRUE
9			2/10/24 0:00	2/8/24 0:00	3/8/24 0:00	FALSE
10			12/29/24 8:00	2/9/24 0:00	3/9/24 0:00	TRUE
11			2/20/24 0:00	2/10/24 0:00	3/10/24 0:00	FALSE
12			1/8/25 8:00	2/11/24 0:00	3/11/24 0:00	TRUE
13
14
Sheet1

Cell Formulas
Range	Formula
C3	C3	=C2+10
C5:C12	C5	=C3+10
F2:F12	F2	=NOT(AND( C2 <= E2, C2 >= D2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C12	Expression	=NOT(AND( C2 <= E2, C2 >= D2))	text	NO

 

[Cubist]

Another option:

Book3
	A	B	C
1	11/24/24 8:00	11/24/24 12:00	11/24/24 8:18
2	11/25/24 8:00	11/25/24 12:00	11/25/24 12:30
3	11/26/24 9:00	11/26/24 17:00	11/26/24 9:01
4	11/26/24 8:00	11/26/24 18:00	11/27/24 13:13
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C4	Expression	=MEDIAN(A1:C1)<>C1	text	NO