If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
conditional formatting
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! |
#2
|
|||
|
|||
conditional formatting
say your date is in A15 ----conditional formal / formula is
=and(A15today()-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! |
#3
|
|||
|
|||
conditional formatting
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! |
#4
|
|||
|
|||
conditional formatting
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! |
#5
|
|||
|
|||
conditional formatting
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! |
#6
|
|||
|
|||
conditional formatting
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(A15today()-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! |
#7
|
|||
|
|||
conditional formatting
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! |
#8
|
|||
|
|||
conditional formatting
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! |
Thread Tools | |
Display Modes | |
|
|