Help with a conditional formula with multiple conditions

Occasional Visitor

I want to apply a conditional formula that highlights A1 if the date in A1 has passed, AND B1 is blank, AND C1 is NOT blank. Can anyone help with how to do this? Thanks!

2 Replies

@JB123500 

Select A1.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=AND(A1<>"",A1<TODAY(),B1="",C1<>"")

Click Format...

Activate the Fill tab.

Select a color.

Click OK, then click OK again.

I used conditional formatting similar to this on a spreadsheet where I wanted to know, in advance, if a renewal date was approaching. You will Todays Date somewhere in the sheet as a constant update and then conditionally format the cell showing the target date using "In the next month" category, highlighting them in red or another colour. This will tell you cells/people that will become overdue so you have time in advance to remedy the issue. You can use similar styles to look at overdue, ie.dates past if you wish, changing the choice to greater than, less than, etc.