Excel Conditional formatting

[Closed]
Report
Posts
2
Registration date
Thursday May 17, 2012
Status
Member
Last seen
May 17, 2012
-
 Josh -
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?



1 reply

Posts
2
Registration date
Thursday May 17, 2012
Status
Member
Last seen
May 17, 2012

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.
What is in column D?
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.
If you don't want blanks to be highlighted, change your formula to

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

Good luck.