Excel Formula

Kido250

I'm trying to create a formula to show me when dates for returning documents are coming up/over due. I've got the basic formula for highlighting the cells when they are coming up/overdue using conditional formating. However I'd like to take it a stage further and when we have returned the documents then they change back to unfilled and non-bold formatting. I've enclosed a mock up of the spreadsheet as I may not be making much sense without it.

Thanks for any help.

Sandtree

Create a new conditional formatting rule by formula (forget the exact name in the Windows version of Excel) for the selected area of =$H2<>"" and then select/leave it as no formatting. Go into Manage Rules of conditional formatting, move it to the top and tick the box at the end that says Stop If True

That way if you have a returned date (ie the return date field isn't blank) it applies the null formatting and doesn't look at the later rules. 

Kido250

Sorry but I think I must be missing something obvious. It doesn't seem to work for me. I've gone into conditional formatting in column D and put the formula in as above, moved it to the top and put the tick to stop if true. I've then put a date in cell H2 but nothing has changed. Apologies if I've missed the obvious. Thanks.

Sandtree

If you were filling the whole of column D, rather than starting from D2 then you'd change it to be =$H1<>"" otherwise you'll have an offset by 1 row

Kido250

Works great now. Thanks. One other question. Is there a formula I can put in so when the cell in column D doesn't have a date in it it stays unformatted?