Formatting cell color if date in the cell is old

[SandsB]

I have a column of dates, column C. If the date in C2, C3, C4, etc. is more than 6 moths old I want the cell to have a white, bold font and a red background. I've tried mucking around with conditional formatting but no luck.

 

[tc88]

I have a column of dates, column C. If the date in C2, C3, C4, etc. is more than 6 moths old I want the cell to have a white, bold font and a red background. I've tried mucking around with conditional formatting but no luck.

Try this out? Under conditional formatting-->New Rule-->Use formula to determine which cells to format

=DATEDIF(C2,TODAY(),"M")>6

 

[Alphacsulb]

Pick a cell and format it to date. Put this formula in there:

=TODAY()-180
This is 6 months ago from today.

Then highlight your cells with dates and conditional format with formula is:
=IF(C1<$E$1,TRUE,FALSE)

Use the format to bold, white, and shade it red. In my example, my 6 month mark is cell E1.

 

[SandsB]

Works great. I did change the 6 to a 5 so it flags it after 6 months instead of 7
Thank you very much

 

[SandsB]

If the cell is blank it makes it red. Is there a way to do this only if the cell has data?

 

[Alphacsulb]

You can add another rule before this one in conditional format. IF cell is blank then nothing.

 

[SandsB]

I added a rule that says
cell value | equal to | 0. I also tried
cell value | equal to | ""

with no luck

 

[Alphacsulb]

Condition 1:
Formula is =IF(C1="",TRUE,FALSE) with cell shading white, font color is white.

Condition 2:
Formulas is =DATEDIF(C1,TODAY(),"M")>5 with your cell shading red, font color white, bold.

 

[SandsB]

I don't get it. This should work for me but I still get red in the empty cells.

 

[Alphacsulb]

Are your cells formatted to date?

Does the same thing happen on a new worksheet?

Condition 1 =IF(C1="",TRUE,FALSE) applies to cell C1 only.

Condition 2 =DATEDIF(C1,TODAY(),"M")>5 applies to cell C1 only.

Double check this.