Excel function query

R

Rodney

Hi everyone,

Somehow i need a formula or something similar which will do the following.
Basically, i have a column (A) of cells with different dates in them (e.g.
11/10/07). I then have another column (B) with some blank cells and some
cells with numerical values in them. What i want to do is make only the blank
cells in column B turn blue if 60 days over the date in Column A passes. For
example, cell A1 contains the following date: 11/07/07 and Cell B1 is blank.
Therefore cell B1 should be blue because 60 days have passed since 11/07/07.

Hopefully someone can help, and that makes sense!

Thanks!
Rod
 
P

Pete_UK

Highlight all the cells in that column that you want to cover,
starting with B1, then click on Format | Conditional Formatting. In
the panel that pops up, select Formula Is rather than Cell Value Is in
the first box, and enter this formula in the second box:

=AND(B1="",A1<TODAY()-60)

Then click the Format button, select the Patterns tab and choose your
shade of blue. Click OK twice to exit the dialogue, and you should
have the cells coloured appropriately.

Hope this helps.

Pete
 
G

Gord Dibben

I think this does what you want.

Select the range in column B

Format>Conditional Formatting>Formula is

=AND(ISBLANK($B1),($A1<TODAY()-60))


Gord Dibben MS Excel MVP
 
Top