Conditional Formatting with date

[Mattlake]

Hi

I hope someone can help me?

I need to do conditional formatting on a cell. I have cell O4 with a date in i.e. 9/9/20, and I need it to got red if the date more than 1 year passed and blue if it is 6 months passed.

The formula i have done is: =$O$4<=TODAY()-182

but if i do another for 365 it override this one.

again it feels that i am making it more difficult than needed

Regards

Matthew

 

[jon1315]

Hi Matthew
Once you've written one rule, you can then go an write a second rule. If you then go to manage rules at the bottom of the conditional formatting menu you can change the order that the rules will apply (which you want to apply in the instance the both rules are satisfied).
Hope this helps
Thanks
Jon

 

[Mattlake]

is there a way to have them constantly running?

i didnt know if i could 'mash' the 2 formulas together

 

[jon1315]

So the conditional formatting will be constantly running, all cells within the range will be formatted whenever the conditions are met. I think maybe I'm not sure what you mean by it?

Re putting into one rule - not without VBA - to do that you'd need a "three way" conditional formatting (over 12months, over 6 months, no formatting) which isn't possible using the standard menu options.

Thanks
Jon

 

[Mattlake]

I think I may have to go visual basic on this one then

Thank you

 

[Fluff]

Is this what you want?

+Fluff 1.xlsm
	A
1
2	09/09/2020
3	01/09/2021
4	01/03/2021
5	08/09/2020
Lists

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A5	Expression	=AND(A2<>"",A2<EDATE(TODAY(),-12))	text	NO
A2:A5	Expression	=AND(A2<>"",A2<EDATE(TODAY(),-6))	text	NO