CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Dinu
Dinu

Reputation: 933

Conditional Formating Comparing date between 2 columns

I need to compare 2 columns say A and B. Both A and B contains dates. If the date in B is less than date in A then I need to colour column B in red colour. I have used this formula ="IF($C$2<$B$2-1)" But it is not formatting the cells. How can we do the above scenario?

Upvotes: -1

Views: 19517

Answers (2)

user4039065
user4039065

Reputation:

Don't use the quotes. As a matter of fact, you don't even need the IF. All you have to do is provide a valid formula that returns either true or false.

First clear out any previous attempts at a CF rule that failed. This is no the time to keep something that is broken in the hope that it can be fixed later.

Next select column B then use Home ► Conditional Formatting ► New Rule. Once the New Formatting Rule dialog is open, choose Use a formula to determine which cells to format and supply the following for the Format values where this formula is true: text box.

=AND($B1<=($A1-1), ROW()>1)    ◄ the date in B is less than the date in A and the row is not the top row

Click Format and select a red Fill. Click OK once to accept the new format then OK again to create the new rule.

Upvotes: 2

user3819867
user3819867

Reputation: 1120

=IF($B2<$A2)

You need to get rid of the dollar signs before the row number. Also, it's A and B not B and C.

Upvotes: 0

Related Questions