Conditional Formatting Help

[Tankmech4]

I need some help with an Excel formula involving conditional formatting. I've googled what I'm trying to accomplish, and used the formula's that others have suggested, the formula's themselves worked, but not the way I desire them too.

What I'm wanting to do is a color fill. Some backstory. Our government vehicles require re-dispatch every 30 days. So from the date of dispatch to 20 days after that date I want the cell green, from day 21 to day 25 I want the cell yellow, and from day 26 to day 30 I want it red. Here is an example:

Lets say the dispatch date is 09/30/2021

So From 09/30/2021 thru 10/19/2021 the cell would be green
From 10/20/2021 thru 10/24/2021 the cell would be yellow
From 10/25/2021 thru 10/30/2021 the cell would be red
Now lets say its re-dispatched on 10/29/2021, once that new date is filled in; the process should restart with a green fill.

Hopefully this explains what I'm attempting well enough.

 

[JGordon11]

Book1
	A	B	C	D
1	Dispatch Date			now
2	8/30/2021			9/30/2021
3	9/1/2021
4	9/3/2021
5	9/5/2021
6	9/7/2021
7	9/9/2021
8	9/11/2021
9	9/13/2021
10	9/15/2021
11	9/17/2021
12	9/19/2021
13	9/21/2021
14	9/23/2021
15	9/25/2021
16	9/27/2021
17	9/29/2021
18
Sheet2

Cell Formulas
Range	Formula
D2	D2	=NOW()
A3:A17	A3	=A2+2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A17	Expression	=NOW()-A2<=20	text	NO
A2:A17	Expression	=NOW() - A2 <= 25	text	NO
A2:A17	Expression	=NOW() - A2 <99999	text	NO

 

[Tankmech4]

Does the =Now and =Today format do the same thing; utilize the automatic date on the computer?

 

[JGordon11]

now() returns current date and time as of the calculation so it will usually be a whole number (unless it is the stroke of midnight) + a decimal fraction, today() returns today at 0000 hours which is a whole number.

So now() might return 44469.75 whereas today() would return 44469 - these numbers are usually formatted as a date but underneath the formatting is just a number.

Yes both use computer's continuously updating date/time value whenever the function is calculated.