Condition format based on date

[amlagu]

Hi.

I was wanting to highlight a cell if the current date is equal to or past the date in another cell if yet another cell is blank. That is, if K6 is 5/10/14, then if there is no value in L6 on 5/10/14 or later then I want to change colour of the cell I6 and (perhaps) write overdue in L6.

Secondly, can I highlight a cell based on a specific date. Ie when the date occurs and there is no value in a cell 7 days prior I want to highlight another cell a colour. ie if there is no value in N6 and the money is due on 1st April 2015, then I want M6 to change to a colour on 28th March 2015. Then on the 1st of April 2015 (and beyond) if there is still no value in N6, I want M6 to change yet another different colour.

Possible? Thanks.

 

[Andrew Poulsom]

You can use formulas in Conditional Formatting. Here's a tutorial:

http://www.contextures.com/xlCondFormat02.html

 

[Special-K99]

Select I6

Conditional Formatting
New Rule
Use a formula to determine...

=AND((K6 > = DATEVALUE("05/10/2014"),L6=""))

format as required
Use Format painter (paintbrush icon) to copy to other cells

Change the formula in L6 to
=IF(AND(K6 > = DATEVALUE("05/10/2014"),"OVERDUE","")

 

[amlagu]

Thank you very much.

Unfortunately in the second formula it says I'm missing a parenthesis.. any ideas