Excel function query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rodney - 22 Nov 2007 14:49 GMT

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

Reply to this Message

Pete_UK - 22 Nov 2007 15:57 GMT

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

> Hi everyone,
>
[quoted text clipped - 10 lines]
> Thanks!
> Rod

Reply to this Message

Gord Dibben - 22 Nov 2007 16:02 GMT

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

>Hi everyone,
>
[quoted text clipped - 10 lines]
>Thanks!
>Rod

Reply to this Message

Rate this thread: