Previous Topic Next Topic

Excel Conditional formatting

Closed
ksickler88 Posts 2 Registration date Thursday May 17, 2012 Status Member Last seen May 17, 2012 - May 17, 2012 at 01:39 PM
 Josh - May 21, 2012 at 02:50 PM
Hello,
I am working with a mid sized spreadsheet (around 500 rows) and Am trying to set the formatting so that if a date is earlier than today, it will highlight it. I am trying to apply this down an entire column (several, actually). I have attempted to follow several other variations of this already posted, but with no success. Any takers?



Related:

1 response

Answer 1 / 1
ksickler88 Posts 2 Registration date Thursday May 17, 2012 Status Member Last seen May 17, 2012
May 17, 2012 at 02:05 PM
I have had a limited degree of success with the following formula so far:
=$D5-420<TODAY()
however, it is still highlighting dates that are later than today, and I am not sure why. Any help would be greatly appreciated.
0
Josh
May 21, 2012 at 02:14 PM
What is in column D?
0
Josh
May 21, 2012 at 02:48 PM
Nevermind, I figured it out. Not sure what you your formula is trying to accomplish, it appears that any date that is 420 days greater than today, then you highlight it. Try these steps. Select a cell in column D, select Conditional Formatting, New Rule, Use a formula to determine which cell to format. Select the box next to the formula box so you can select cells, select the same cell in column D, remove the "$" from before the number, and complete your forumla. Click the box next to the formula box, click format, select your highlight color, then OK. Click on Conditional Formatting, Manage rules, change the Applies to, from =$D$#, to =$D:$D. Click apply. Done.
0
Josh
May 21, 2012 at 02:50 PM
If you don't want blanks to be highlighted, change your formula to

=AND($D1<Today(), $D1<>"")

Good luck.
0

Similar discussions

Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses