Prev: How can I set up a shortcut key for Autosum?
Next: Average with Conditions
From: Teeny on 13 May 2010 11:00
I have a range of dates in a column and I would like it if I could highlight
any date that is more that 3 years old.

So if the date reads 02/10/2004 this should be highlighted red.

I would really appriciate a bit of help.
From: Luke M on 13 May 2010 11:11
CF formula is:

=DATEDIF(A2,TODAY(),"y")>=3

--
Best Regards,

Luke M
"Teeny" <Teeny(a)discussions.microsoft.com> wrote in message
news:CA22E7DF-C343-4059-88B9-1A2C538867B1(a)microsoft.com...
>I have a range of dates in a column and I would like it if I could
>highlight
> any date that is more that 3 years old.
>
> So if the date reads 02/10/2004 this should be highlighted red.
>
> I would really appriciate a bit of help.


From: Jacob Skaria on 13 May 2010 11:13
1. Select the cell/Range (say A1:A10). Please note that the cell reference A1
mentioned in the formula is the active cell in the selection. Active cell
will have a white background even after selection
2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the below formula
=AND(A1>0,DATEDIF(A1,TODAY(),"y")>3)

4. Click Format Button>Pattern and select your color (say Red)
5. Hit OK

PS: If you are using XL2007 Goto Home tab>Styles>Conditional
Formatting>Manage rules>New rule>Use a formula to determine which cells to
format. Enter the formula in the box below.

--
Jacob (MVP - Excel)


"Teeny" wrote:

> I have a range of dates in a column and I would like it if I could highlight
> any date that is more that 3 years old.
>
> So if the date reads 02/10/2004 this should be highlighted red.
>
> I would really appriciate a bit of help.
 | 
Pages: 1
Prev: How can I set up a shortcut key for Autosum?
Next: Average with Conditions