Closing dates

[Guest]

We have a spreadsheet with closing dates on it. We'd like the closing date
field's pattern to turn red when today after the closing date.

BUT, once a date is input into another field along the same row, we'd like
the first field to revert to its original colour.

Can anyone help please?

 

[Bob Phillips]

Use conditional formatting with a formula of

=AND(B2="",A2<TODAY())

where A2 is the closing date, B2 is the other field.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob

Thanks for that, it works a treat.

Only thing is, if the closing date field is empty, then the field also turns
red. Is there a way around this, so that the field only turns red if there is
a closing date and it has expired?

Thanks.

 

[Bob Phillips]

=AND(A2<>"",B2="",A2<TODAY())


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob.

Now that really has done the trick. many thanks.

I've got another similar problem now:

We have a person's name coloured according to the month they start work.
We'd like that colour to revert to pale grey once we enter a date in another
field on the same row.

This sounds like the same issue, but I tried using one of your earlier
formulae and that doesn't do it.

Thanks.

 

[Bob Phillips]

I assume that you use normal cell colouring for the month colouring, as CF
cannot support 12?

So just add a CF formula of

=J2<>""

as an example, and format as grey

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob

Many thanks for that.

I assume that you use normal cell colouring for the month colouring, as CF
cannot support 12?

So just add a CF formula of

=J2<>""

as an example, and format as grey

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)