Conditional Formatting Expired Dates

[ExcelRoy]

Hi all,

I am trying to highlight expired/ expiring dates using CF

I have the following formula =AND(LEN(G13),TODAY()>G13)

This doesn't seem to work with a range of cells and only highlights parts of the range

Any ideas what I am doing wrong?

Thanks

 

[ExcelRoy]

Think I got it, it looks like the formula is checking each cell within the range

how do I highlight a C:J if the condition is true?

Many thanks

 

[Marcelo Branco]

Select C13:J13 and in CF try this formula

=AND(LEN($G13),TODAY()>$G13)

M.

 

[ExcelRoy]

Many thanks Marcelo, works perfect,

is there a way to use this formula to highlight a day before TODAY?

 

[Fluff]

That's what the formula does!

 

[ExcelRoy]

Hi Fluff,

Can I use a similar formula to highlight

Red - Anything past TODAY
Green - Today
Yellow - 1 Day Before TODAY
Orange - 2 Days before TODAY

Many thanks

 

[Fluff]

When you say

Anything past TODAY

do you mean older than today, or sometime in the future?

 

[ExcelRoy]

Hi Fluff,

Older than today (expired)

many thanks for your help Fluff

 

[Fluff]

The red formula is the one you already have.
For green use
=AND(LEN($G13),TODAY()=$G13)
yellow
=AND(LEN($G13),TODAY()-1=$G13)
orange use the above but change -1 to -2
But ensure that the red rule is the last in the list, otherwise it will overall the yellow & orange rules

 

[ExcelRoy]

perfect Fluff, many thanks

Did you get a chance to look a my previous problem with the priority settings?

Thanks again