Formula based on two conditions?

[LMills77]

Hi Everyone,

I need a conditional format that is based on two cells.

I have a To Do list in Excel; one column is 'Anticipated Date of Task' and the second column is 'Date Completed.'

If a task is overdue, the entire row turns the font red. So far so good.

However, if the task is completed, I don't put a date in the 'Anticipated Date of Task' but instead put the date in 'Date Completed.' The problem is that the row font is still red.

If there a formula I can write that if there is a date in 'Week Completed' it will override the Red format and return is as the normal font?

I can put a dash in there and it solves the problem, but curious to know if a formula can be written to complete the same thing?

Thank you,

Lee.

[TMS]

Use AND to add the second condition ... Date Completed = ""

[Jacc]

If you post a workbook with sample data and expected results it will be much easier to help you.

[LMills77]

Hello,

Thanks for getting back to me.

I've posted the workbook to show you what I mean.

As you can see in Column E, I have 'Completed' but because column D is blank, it shows are Red Font. However, I would like a formula to consider the completed date in Column F to change the font format to normal.

The formula conditional formatting I've used is: =$D2< TODAY()

I assume I can add to this to complete what I want to do?

Thank you,

Lee.

[AliGW]

How about this?

=AND($D2 < TODAY(),$D2 < > "")

I've added spaces so that I can post the formula here (the forum doesn't like chevrons) - please remove these when you replicate it.

[LMills77]

How about this?

=AND($D2 < TODAY(),$D2 < > "")

I've added spaces so that I can post the formula here (the forum doesn't like chevrons) - please remove these when you replicate it.

Hi,

Thanks for getting back to me.

So how does the second part of the formula work?

I've managed to find a workaround, which is formatting the 'Completed' rule to return normal font formatting. It's worked fine.

But I'd still like to know how the AND and second part of the formula works.

Thanks again,

Lee

[AliGW]

The formula is saying:

Format cells that are earlier than today AND not equal to blank.

Syntax:

=AND(first_criterion,second_criterion,...)

[TMS]

Think it should be this: =AND($D2 < TODAY(),$E2 = "")

[AliGW]

Good spot, Trevor.

Anything to limit the number of CF rules that the OP needs to set up and maintain. They should be kept to an absolute minimum.

[LMills77]

Hello,

Thank you for getting back to me. I appreciated. it.

Lee.

[TMS]

You're welcome.