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!

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


"Teeny" wrote:

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!

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

"Teeny" wrote:

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!

Hi Teeny

Select your range of dataFormatConditional Formattingform 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
--
Regards
Roger Govier

Teeny wrote:
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!

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 FormatConditional FormattingCondition 1Formula 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 HomeConditional FormattingManage Rules
OR
Alt+O+D
Click New RuleSelect Use Formula to determine which cell to FormatFormat
Values Where This Formula is True Copy and paste the below formula
=AND($A1<"",DATEDIF($A1,TODAY(),"Y")=3)
Click OkApplyOk.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"Teeny" wrote:

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!

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


"מיכ�ל (מיקי) �בידן" wrote:

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


"grizzly6969" wrote:

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


"Teeny" wrote:

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!

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.

"RonaldoOneNil" wrote:

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

"Teeny" wrote:

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!

Please check your other post.

--
Jacob (MVP - Excel)


"Teeny" wrote:

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!