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

✔ 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!

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 OKHow 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

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.

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

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!

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/2012Change color.

Is this what your looking for?

MIKE

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

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!

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History