Conditional formatting using IF And

[android1]

Need to apply conditional formatting to a cell containing a date

e.g L6 = (08/06/2019)



Criteria are:



If L6 is 2 months or less away from todays date & If M6 is Blank then


L6 should be filled red.


If L6 is 1 month or less away from todays date & If M6 is Blank then


L6 should be filled blue



(Is it possible to have the cell flash rather than fill a colour. Ok if not possible)

Sample Sheet -> https://www.dropbox.com/s/808vub4morxq7gt/Date Renewal.xlsx?dl=0

 

[strooman]

First, you should review your criteria because they can give the same result. When L6 is 2 months or less away from today it's also 1 month or less away from today.
Second, in your example you give L6=08/06/2019. Is 2019 a typo?
Third, L6 can be in the future or in the past from todays date. Better is to view if L6 is > (bigger) or < (smaller) then today
Fourth, flashing cells can only be achieved with VBA.

That said, here's a an example to get you going. Directions in column F.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr][tr][td]

1​

[/td][td="bgcolor:#0070C0"]Between 60 and 30 days[/td][td="bgcolor:#0070C0"]Between 29 and 0 days[/td][td="bgcolor:#0070C0"]Date[/td][td="bgcolor:#0070C0"]Today[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

1-6-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]1. Select C2:C14[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

FALSE​

[/td][td]

FALSE​

[/td][td]

7-6-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]2. Conditional formatting[/td][td][/td][/tr]
[tr][td]

4​

[/td][td="bgcolor:#FFDDDD"]

TRUE​

[/td][td]

FALSE​

[/td][td="bgcolor:#FFEBEB"]

8-6-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]3. New Rule[/td][td][/td][/tr]
[tr][td]

5​

[/td][td="bgcolor:#FFDDDD"]

TRUE​

[/td][td]

FALSE​

[/td][td="bgcolor:#FFEBEB"]

5-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]4. Use a formula to determine which cells to format[/td][td][/td][/tr]
[tr][td]

6​

[/td][td="bgcolor:#FFDDDD"]

TRUE​

[/td][td]

FALSE​

[/td][td="bgcolor:#FFEBEB"]

6-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]5. Type the first formula[/td][td][/td][/tr]
[tr][td]

7​

[/td][td="bgcolor:#FFDDDD"]

TRUE​

[/td][td]

FALSE​

[/td][td="bgcolor:#FFEBEB"]

7-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td]6. Redo 1 till 5 for thr second formula[/td][td][/td][/tr]
[tr][td]

8​

[/td][td="bgcolor:#FFDDDD"]

TRUE​

[/td][td]

FALSE​

[/td][td="bgcolor:#FFEBEB"]

8-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td] =AND(D2-C2<=29;D2-C2>=0)[/td][td="bgcolor:#E7F5FF"]BLUE[/td][/tr]
[tr][td]

9​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

9-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td] =AND(D2-C2<=60;D2-C2>=30)[/td][td="bgcolor:#FFEBEB"]RED[/td][/tr]
[tr][td]

10​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

10-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

11-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

12-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

13-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td]

FALSE​

[/td][td="bgcolor:#E7F5FF"]

TRUE​

[/td][td="bgcolor:#E7F5FF"]

14-7-2018​

[/td][td]

7-8-2018​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[jtakw]

Hi,

You need to be more specific.

Do you mean if L6 is 2 months or less away from today "In the Past", or "In the Future"?

Also, the dates in your sample are inconsistent in format, some are mm/dd/yyyy, and some are dd/mm/yyyy