Formula for red, yellow, green formatting

[midcoastchris04]

Hey Friends!

I am trying to recreate a formula I had at a prior job that color codes tasks (red, yellow, or green) based on age. For the dates below, I need anything 24 hrs or older from "now" to be red. Anything within 4 hrs of hitting the "red" to be yellow, and anything else to be green. Any help is greatly appreciated!

10/27/2020 8:51

10/27/2020 9:37

10/27/2020 11:21

10/27/2020 11:31

10/27/2020 11:51

 

[etaf]

you would need 3 rules in conditional formatting - BUT the colour will not change with the spreadsheet just left open as it needs a calc to re evaluate the conditional formatting
If you need it to change live when open , then you will need VBA

Book1
	A	B	C
1			Colour
2	10/28/20 10:51		green
3	10/27/20 9:37		RED
4	10/27/20 11:21		RED
5	10/28/20 11:31		green
6	10/29/20 11:51		green
7	10/27/20 16:44		yellow
Sheet4

Cell Formulas
Range	Formula
A7	A7	=NOW()-TIME(21,0,0)
C2:C7	C2	=IF(A2<NOW()-1,"RED",IF(A2<NOW()-TIME(20,0,0),"yellow","green"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=AND(A1<>"",A1>NOW()-TIME(20,0,0))	text	YES
A:A	Expression	=AND(A1<>"",A1<=NOW()-1)	text	YES
A:A	Expression	=AND(A1<>"",A1<=NOW()-TIME(20,0,0))	text	YES