Time difference with conditional formatting

[Smarahj]

Hello!

I need help with a formula. I need to highlight in red a cell that has an hour time difference. Start time for a driver is 03:00. They are out of hours at 17:00. The estimated completion time for their route is 1900. I need the cell to highlight as red if it is within 1 hour of 1700. I have not been able to find anything that matches what I'm looking for. Help!

 

[AhoyNC]

Welcome to the forum.

Maybe something like this.
-Conditional Formatting
-New Rule
-Use a formula
For formula enter (change time range to match your data)
=AND(A2>=16/24,A2<=18/24)

Book1
	A
1	Time
2	15:00
3	16:05
4	19:00
5	18:30
6	17:56
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A6	Expression	=AND(A2>=16/24,A2<=18/24)	text	NO

 

[Smarahj]

Maybe it's because I'm looking at this super early but it's just not clicking with me. These are the columns in my spreadsheet. I need the HOS column to have the conditional formatting if the estimated completion time is within 1 hour of HOS.

 

[AhoyNC]

Try:
-Conditional Formatting
-New Rule
-Use a formula
For formula enter (change time range to match your data)
=(IF(B2<A2,A2-B2,B2-A2)*1440)<=60

Book1
	A	B
1	HOS	Est. Comp.
2	17:35	18:19
3	17:01	16:31
4	15:49	17:29
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=(IF(B2<A2,A2-B2,B2-A2)*1440)<=60	text	NO

 

[Smarahj]

That worked.. somewhat. It turned every cell red, even those that had plenty of hours remaining.

This was the formula used, as adjusted for the correct cell number
=(IF($P$6<$O$6, $O$6-$P$6,$P$6-$O$$A$3:$P$816)*1550)<=60

Do I need to put in the time for each cell in the formula or can I apply the formula to the whole column?

 

[AhoyNC]

Is this what you want? If not which cells should be red and why.
For this example I chose cells M4 to M17.
Formula I used:
=IF(M4="","",(IF(N4<M4,M4-N4,N4-M4)*1440)<=60)

Book1
	H	I	J	K	L	M	N
1
2	Delaytrne	Total Stops	Remainir&	Completed Stoe	Actual Start Tire	HOS	Estmated Completiore
3	*
4		15		15	3:59	17:59	17:34
5		16		16	4:16	18:16	18:40
6		15		15	1:50	15:50	16:11
7		19		19	12:08	2:08	16:16
8		9		9	2:06	16:06	16:16
9		12		12	4:17	18:17	15:16
10		16		16	3:10	17:10	16:58
11		12		12	4:04	18:04	16:15
12		17		17	4:31	18:31	19:07
13		17		17	4:21	18:21	15:59
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:M17	Expression	=IF(M4="","",(IF(N4<M4,M4-N4,N4-M4)*1440)<=60)	text	NO

 

[Smarahj]

Is this what you want? If not which cells should be red and why.
For this example I chose cells M4 to M17.
Formula I used:
=IF(M4="","",(IF(N4<M4,M4-N4,N4-M4)*1440)<=60)

Book1
	H	I	J	K	L	M	N
1
2	Delaytrne	Total Stops	Remainir&	Completed Stoe	Actual Start Tire	HOS	Estmated Completiore
3	*
4		15		15	3:59	17:59	17:34
5		16		16	4:16	18:16	18:40
6		15		15	1:50	15:50	16:11
7		19		19	12:08	2:08	16:16
8		9		9	2:06	16:06	16:16
9		12		12	4:17	18:17	15:16
10		16		16	3:10	17:10	16:58
11		12		12	4:04	18:04	16:15
12		17		17	4:31	18:31	19:07
13		17		17	4:21	18:21	15:59
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M4:M17	Expression	=IF(M4="","",(IF(N4<M4,M4-N4,N4-M4)*1440)<=60)	text	NO

View attachment 108055

That is what I was looking for! I'll give a try. Thank you!