conditional formatting

[Teeny]

I have a column of dates and I want to highlight in red all dates that are
equal to and more than 3 years old

For example if the date is 02/10/04 this should be highlighted in red.

I would appriciate anyones help!

 

[grizzly6969]

say your date is in A15 ----conditional formal / formula is
=and(A15<today()-1095)
and format to your colour

 

[RonaldoOneNil]

Try setting the condition to
Cell Value is
Not Between
=TODAY()
=TODAY()-(365*3)

 

[Roger Govier]

Hi Teeny

Select your range of data>Format>Conditional Formatting>form dropdown
choose Formula is> =DATEDIF(A1,TODAY(),"y")>=3
Choose Red Font

Change A1 to the appropriate reference for the range of data you choose

 

[Ms-Exl-Learner]

Assume that you are having the Date Values in A Column. Place the cursor in
A1 cell and press Cntrll+Spacebar and now the total A Column will be getting
selected. A1 should be the active cell (Active Cell will have a white
Background after selection also).

Excel 2003:-
Select Format>>Conditional Formatting>>Condition 1>>Formula Is>>
=AND($A1<>"",DATEDIF($A1,TODAY(),"Y")>=3)
Then Click the Format command button and choose your desired Font and
Pattern colour and give Ok…

Excel 2007:-

Click Home>>Conditional Formatting>>Manage Rules
OR
Alt+O+D
Click New Rule>>Select Use Formula to determine which cell to Format>>Format
Values Where This Formula is True>> Copy and paste the below formula
=AND($A1<>"",DATEDIF($A1,TODAY(),"Y")>=3)
Click Ok>>Apply>>Ok.

Remember to Click Yes, if this post helps!

 

[grizzly6969]

you are absolutely correct I didn't think of a leap year
--
grizz

Usually, Excel is used for precise calculations - therefor I don't think the
suggestion to reduce 1095 days (365*3) is a good idea because of some leap
years with 366 days [such as 2008].
Micky

say your date is in A15 ----conditional formal / formula is
=and(A15<today()-1095)
and format to your colour

 

[Teeny]

Thanks, this is similar to thow I have been doing it, however I need to
subtract full years and not 365 days as I realised this does not account for
leap years.

 

[Jacob Skaria]

Please check your other post.