conditional formatting highlight dates 90 days or less
michelle
I'm trying to figure out how to make conditional formatting highlight any
dates in a spreadsheet when they are within 90 days or less. I've looked
forever in the help files, etc but can't find what i'm looking for. HELP?
Sean Timmons
Assuming your first cell to evaluate is in A2:
In your conditional formatting, select Formula Is, then
=ABS(TODAY()-A2)<90
michelle
Sean, I tried using your formula but with the -A2 value as -D2:D55 because
the column that has the dates that I want to use the formula on are D2
through D55. so my formula looked like this
Formula is =ABS(TODAY()-D2:D55)<90
It didn't work. how do I get the formula to work for D2 through D55?
BTW -- thank you for your help! I really appreciate it
Sean Timmons
So, you're going to want to check formatting in cells D2 through D55..
In D2, in conditional formatting, type
=ABS(TODAY()-D2)<90
Then copy and paste special/formats down to D55. This will automatically
update your conditional format to check D3, D4, etc. against TODAY()
Francis
try this
highlight the range or column
in CF under Formula Is, place this formula
=(TODAY()-A2)<=90
go to Format >> select your format / color
OK
--
Hope this is helpful
Pls click the Yes button below if this post provide answer you have asked
Thank You
cheers, francis
Am not a greek but an ordinary user trying to assist another
michelle
I think I figured it out. I highlighted D2 through D55 and I made the formula
=ABS(TODAY()-D2)<90
then it worked for all of those cells. WOOHOO! finally-after an entire day
working on this and going through tutorials etc.
Do you know where I can go to learn how to make these formula's so I know
what why I just typed those things into the formula?
Shane Devenshire
Hi,
The formula you use depends on your definition of 90 days or less. Unless
you are looking for date within the past OR future 90 days you may need a
different approach.
To conditionally format your cell(s):
In 2003:
1. Select the cells you want to format
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=AND(TODAY()+90-D2<=90,TODAY()+90-D2>0)
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.
In 2007:
1. Highlight all the cells on the rows you want formatted, in your case D2:D55
2. Choose Home, Conditional Formatting, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
=AND(TODAY()+90-D2<=90,TODAY()+90-D2>0)
5. Click the Format button and choose a format.
6. Click OK twice
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire
Shane Devenshire
Hi,
If what you are looking for is dates within 90 days or less on either side
of today then the previous suggests each provide part of the solution:
=ABS(TODAY()-A2)<90
=(TODAY()-A2)<=90
instead use
=ABS(TODAY()-A2)<=90
--
If this helps, please click the Yes button.
Cheers,
Shane Devenshire