Conditional formatting. Allow for blank cells

**simesPSB** · 01-09-2011, 07:34 PM

Please help. I've tried various formulas to no avail (i'm no expert!)

In column F, I have a series of dates.. It is formatted as a date and appears dd/mm/yy. In column L, I have conditional formatting that changes the cell colour to amber or red if todays date is 28 or 56 days from the date inputted into F. So far so good.
If there is no information in F5 (the next free row), I want no information in L5 either. However I am getting 28/01/00 with the cell filled in red. I assume that it is seeing a date in F5 even though I am not? How can I get my unused lines to be completely empty.

I've tried to be clear, hope this is ok.

**TMS** · 01-09-2011, 07:45 PM

Change the formula to: =IF(F5="","",F5+28) in cell L5

Copy that up and down.

You'll also need to change the conditional formatting:

For the red condition: =AND(F3<>"",TODAY()>F3+56)

For yellow: =AND(F3<>"",TODAY()>F3+28)

Regards

**NewGuy OnBlock** · 01-09-2011, 07:45 PM

I have read the forum rules, your title need to be edited to refelct on your question.

**Richard Buttrey** · 01-09-2011, 07:46 PM

Hi, and welcome to the forum.

Please note the forum rules which require thread titles to be meaningful. Just saying "formula help" is no good. We know you want help otherwise you wouldn't be here. I'll change it on this occasion.

You'll always get a result in L5 of 28 since there's no if test to do anything else. Use instead

Please Login or Register  to view this content.

And then you'll need to add another conditional format along the same lines to show a white background.

HTH

**simesPSB** · 01-10-2011, 09:12 AM

Originally Posted by TMShucks

Change the formula to: =IF(F5="","",F5+28) in cell L5

Copy that up and down.

You'll also need to change the conditional formatting:

For the red condition: =AND(F3<>"",TODAY()>F3+56)

For yellow: =AND(F3<>"",TODAY()>F3+28)

Regards

Thanks for this, I feel like I am getting somewhere. However, I now get no dates appearing in L5 under any circumstance. I need todays date plus 28 days to appear (as well as yellow/red colours when overdue by 28/56 days). I need it to have nothing if F5 is empty.

**simesPSB** · 01-10-2011, 03:46 PM

Originally Posted by simesPSB

Thanks for this, I feel like I am getting somewhere. However, I now get no dates appearing in L5 under any circumstance. I need todays date plus 28 days to appear (as well as yellow/red colours when overdue by 28/56 days). I need it to have nothing if F5 is empty.

Apologies. I obviously did something wrong earlier. It works perfectly. Thanks so much for your help!