COnditional Formatting with Multiple Criterias

0
votes

I have been trying to do Conditional formatting for a Column of Date. Column A: Actual end date , Column B : Proposed End date.

Column A dates should be formatted to Red Font Automatically if the dates are greater than the proposed dates on Column B and also if the dates are less than ( ex.: 18.5.2015 ) today's date (22.5.2015) .

I tried the Formula ,"=A2>B2, A2< TODAY() " in the formatting manager dialogue box.

Thanks in advance.

1
excelconditional-formatting

1 Answers

0
votes

When assigning conditional formulas, you need to use absolute references for the column or row that you wish to hold constant. Additionally, the AND() formula is needed to provide the Boolean AND that your code requires. The following snippet should work:

=AND($A2>$B2,$A2<TODAY())

One additional comment: I would avoid using TODAY() or other volatile formulas in conditional formatting. On large worksheets, this can seriously impact performance. My recommendation would be to create a cell with the formula =TODAY() and reference that cell in your formula, as shown in the following example (the cell containing today's date has been named Today):

=AND($A2>$B2,$A2<Today)