Conditional formatting help

[Fegal]

Hi All

Iam pulling my hair out, surely this is simple??
Can H12 be green if more than 14 hours, orange if less than 14 hours and red if less than 1 hour from now..

Fatigue 1.xlsx
	D	E	F	G	H
6	CURRENT TIME
7	13:19
8
9	End of rest pre deployment	8 hours rest	Deployed
10	Date & Time	Y/N	Date & Time	Pre-deployment wake time	17 Hour limit
11
12	5/04/2024 5:30		5/04/2024 17:00	11	5/04/2024 22:30
13
14	5/04/2024 6:30	y	5/04/2024 17:00	10	5/04/2024 23:30
15	5/04/2024 7:00	y	5/04/2024 17:00	10	6/04/2024 0:00
16	5/04/2024 8:00	y	5/04/2024 17:00	9	6/04/2024 1:00
17	5/04/2024 6:30	y	5/04/2024 17:00	10	5/04/2024 23:30
18	5/04/2024 5:30	y	5/04/2024 17:00	11	5/04/2024 22:30
Phase 1

Cell Formulas
Range	Formula
D7	D7	=NOW()
D12	D12	=IF(D14="","",MIN(D14:D28))
G12,G14:G18	G12	=IF(OR(D12="",F12=""),"",TEXT(F12-D12,"[H]"))
H12,H14:H18	H12	=IF(D12="","",D12+17/24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12	Expression	=I8>=0.583333	text	NO
H12	Expression	=I8>=0.708	text	YES
H12	Expression	=I8=0	text	YES
H12	Expression	=I8<0.58333	text	NO
E12:E28,E33:E70,E88:E120	Cell Value	contains "N"	text	NO

 

[Fegal]

well that embarrising. cells are H12 not I8.. have changed it but its still not working correctly

 

[RobOrBob]

Thats a shame - can't see the values in column I

Your conditioning is a bit confusing. For example, if I8 is 0.9 it would be orange then red that are true.
A more logical set of conditions are:

H12

Expression

=I8>=0.708

text RED

YES

H12

Expression

=I8>=0.583333

text ORANGE

YES

H12

Expression

=I8>0

text GREEN

NO

 

[Fegal]

Thanks for the replyRoborBob..

can you explain like im 5? im struggling with this one.
I can understand why H12 is orange and not red.
I would have thought it would have turned red at 5/4/24 22:30, orange at 5/4/24 19:30 and green before that.

Fatigue 1.xlsx
	H
12	5/04/2024 22:30
13
14	5/04/2024 23:30
15	6/04/2024 0:00
16	6/04/2024 1:00
17	5/04/2024 23:30
18	5/04/2024 22:30
Phase 1

Cell Formulas
Range	Formula
H12,H14:H18	H12	=IF(D12="","",D12+17/24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12	Expression	=H12>=0.583333	text	YES
H12	Expression	=H12>=0.708	text	YES
H12	Expression	=H12=0	text	NO
H12	Expression	=H12<0.58333	text	NO

 

[Fegal]

Heres another way the internet suggested but i cant seem to get it to work

Fatigue 1.xlsx
	D	E	F	G	H
10	Date & Time	Y/N	Date & Time	Pre-deployment wake time	17 Hour limit
11
12	6/04/2024 5:30		6/04/2024 5:30	0	6/04/2024 22:30
13
14	6/04/2024 6:00		6/04/2024 6:00	0	6/04/2024 23:00
15	6/04/2024 5:30		6/04/2024 5:30	0	6/04/2024 22:30
16	6/04/2024 5:30		6/04/2024 5:30	0	6/04/2024 22:30
Phase 2

Cell Formulas
Range	Formula
D12	D12	=IF(D14="","",MIN(D14:D28))
F12	F12	=MIN(F14:F28)
G12,G14:G16	G12	=IF(OR(D12="",F12=""),"",TEXT(F12-D12,"[H]"))
H12,H14:H16	H12	=IF(D12="","",D12+17/24)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H12	Expression	=IF((H12-(NOW()-TODAY()))*1440>60,TRUE)	text	NO
H12	Expression	=IF((H12-(NOW()-TODAY()))*1440>840,TRUE)	text	NO
H12	Expression	=IF((H12-(NOW()-TODAY()))*1440<840,TRUE)	text	NO
E12:E28	Cell Value	contains "N"	text	NO

 

[RobOrBob]

A date/time is actually a number where the integer is the date and the decimals the time.

So to just examine the time you need to get rid of the integer.

Your original conditions should remove the integer before testing e.g.:
=H12-TRUNC(H12)>=0.583333

instead of:
=H12>=0.583333

Your last attempt needs better bracketing to ensure things are calculated in the right order.

 

[Fegal]

Thanks for your efforts.
understand the trunch, but i need to consider the date and time.
not sure how to improve the bracketing on the second attempt.

the original =H1>=058333 etc still isnt giving the correct output.
Guess ill put this in the too hard basket.