Past Date Conditional Formatting

[Atkey]

Hello,

I am attempting to conditional format a number of past dates. How can I get dates:

• From the date in the cell red for 6 months
• From the date in the cell amber from the 6 month point to 12 months
• Un formatted if more than a year ago.

Cheers!

 

[Vorkosigan]

For red:
=AND([target cell]<TODAY(), [target cell]>= TODAY()-182)

For amber:
=AND([target cell]<TODAY()-182,[target cell]>=TODAY()-365)

If I understood your question, this should do what you need it to. If you want the red to highlight today's date as well, change the first < to <=.

 

[kvsrinivasamurthy]

A2= Given date
For red

=AND(A2<TODAY(),A2>=EDATE(TODAY(),-6))

For amber

=AND(A2<EDATE(TODAY(),-6),A2>=EDATE(TODAY(),-12))

 

[Atkey]

A2= Given date
For red

=AND(A2<TODAY(),A2>=EDATE(TODAY(),-6))

For amber

=AND(A2<EDATE(TODAY(),-6),A2>=EDATE(TODAY(),-12))

This is spot on. Thank you!

 

[kvsrinivasamurthy]

Welcome.