Solved Need help with conditional formatting for dates

October 2, 2012 at 18:10:30
Specs: Windows 7
I need to be alerted to a review expiring in 10 days. The reviews are done every 3 months but I would like it to alert me when it is 10 days from expiring so I can schedule the new review on time. How can I write the formula so that excel highlights the cells with conditional formatting? Each date in the entire column is different. I already tried to do so for 1 cell and the formula works but I can't get it to apply to the entire column. Help!
BTW....I am using Excel 2007

See More: Need help with conditional formatting for dates

Report •


✔ Best Answer
October 3, 2012 at 18:40:28
I figured out that this will only work if the formula is only sitting in the first cell (i.e. H2). The formula is applied to $H$2:$H$131 but I wrote it:
=H2+80<=TODAY().
Thanks for your help!


#1
October 3, 2012 at 05:14:24
Try this:

With your Dates in Column A

Conditional Formatting 2007

1) Select your cell or range of cells IE A1:A100
2) On the ribbon click Conditional Formatting
3) Click on New Rules, it’s near the bottom of the dialog box.
4) Click Use Formula to determine which cells to format.
5) Enter the formula:

=A1+10>=TODAY()

6) Click on the Format button
7) Select the Fill Tab
8) Select a pretty color
9) Click OK
10) Click OK

How does the processing know the Review Date is past
and the Review has been done?
You will need some way of turning off the CF,

MIKE

http://www.skeptic.com/


Report •

#2
October 3, 2012 at 16:55:31
I already tried it and it didn't work. I have data in column H from 2 to 121. This is a list that is added on to daily. I just need the change in color so I can distinguish the ones that are going to be expire in 10 days. Then I can schedule the review, and once the review is done, I will manually enter the new date.

Report •

#3
October 3, 2012 at 17:04:42
The above formula worked for me, so I'm not sure why it is not for you.
Post a small example of your data, but first read this How-To which explains how to post your data in this forum:

http://www.computing.net/howtos/sho...

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
October 3, 2012 at 17:14:25
Review (COLUMN H)
7/20/2012
8/15/2012
9/11/2012
9/11/2012
8/24/2012
8/9/2012
9/18/2012
6/13/2012
7/31/2012
9/12/2012
9/18/2012
9/12/2012
9/12/2012
9/19/2012
9/13/2012
9/11/2012
9/11/2012
9/17/2012
8/30/2012
6/6/2012
9/21/2012
9/21/2012
9/21/2012
9/14/2012
9/18/2012
9/19/2012
9/17/2012
9/27/2012
9/27/2012
10/1/2012
9/28/2012
The fx i put in looks like this (made adjustments to the column referenced)
=H2+10>=TODAY()
It should be, whatever date I put in + 80 days - since the review expires in 90 days. I would like it to be formatted 10 days before so I can schedule a new review date prior to expiration.

Any help? Thanks in advance!


Report •

#5
October 3, 2012 at 17:58:12
Using your data in Column H,
and using the formula: =H2+10>=Today()

The bottom four dates:

9/27/2012
9/27/2012
10/1/2012
9/28/2012

Change color.

Is this what your looking for?

MIKE

http://www.skeptic.com/


Report •

#6
October 3, 2012 at 18:35:55
Try this and see if it's what your looking for:

=TODAY()-10>=H2+80

This will highlight only the dates:

6/13/2012 & 6/6/2012

MIKE

http://www.skeptic.com/


Report •

#7
October 3, 2012 at 18:40:28
✔ Best Answer
I figured out that this will only work if the formula is only sitting in the first cell (i.e. H2). The formula is applied to $H$2:$H$131 but I wrote it:
=H2+80<=TODAY().
Thanks for your help!

Report •

Ask Question