I'm trying to use conditional formatting to highlight an entire row if the date in column D is greater than the date in column B. I can't seem to make it work. Is it a problem because it's a date field?
Excel conditional formatting to highlight entire row if one date is not equal to another date in that row
conditional formattingdate timemicrosoft excel
Related Solutions
there is all sorts wrong, unfortunately!
ADDRESS
gives you the address of the cell, not its value... so you are comparing whether an address is bigger/smaller- You have both checks as
>=
rather than one greater and one less than - You have a mix of
,
and;
(but I guess that is probably just editing onto this site!)
To enter the formula:
- go into cell
F6
- Conditional formatting > new rule > use a formula...
=AND(F$4>=$D6,F$4<=$E6)
- This format can then be copied and pasted into your gantt body
To explain:
- in Conditional formatting, Excel will modify the cell references of non-absolute referenced cells. The formula you enter is the one for the top-left of the range that the rule applies to
- We can fix the row or column with the
$
in the address
From my understanding, you want to do a conditional formating of every cell in column x1 if it's value is higher than it's corresponding cell in column x2. To do it :
First, setup your conditional formating on the first cell
- Click on your first cell
- Go in Conditional formating -> New rule
- In the Conditional Formatting Rules Manager, click New Rule...
- Click Use a formula to determine which cells to format
- Enter the condition for the formatting under Format values where this formula is true :
- You can enter any formula you want
- Just keep in mind that if the result of the formula is true, the conditional formatting will be applied and if not it will not be applied
- In your formula, use or remove all the dollar signs
$
you want so that the formula would usually update when the cell is copied if you were copying cells in Excel. - See this article absolute vs relative cell reference in Excel
- If you are not sure if your formula returns TRUE or FALSE or if the result is always as you want it, you can try it in column x3 to see if it works.
- So, in your example, your conditional formatting formula would be
=$A1>$B1
or=A1>B1
. It is important that there is not$
in front of the1
.
- Then set all the format you want in your conditional formatting using the Format button.
- Accept your conditional formatting (Ok button) and go back to the Conditional formatting rules manager window. You will see your new rule.
Apply your rule to the area you want
There are 2 ways to do it :
In the Conditional formatting rules manager window, under the Applies to column, change the range you want your conditional formatting to be applied to the whole range you want.
- If your conditional formatting formula was well done, it will apply and update to the whole range even if the interface does not give you any clue about it.
Directly in the worksheet (Closing the Conditional formatting rules manager), copy the format of your first cell to all the rows you want. There are many ways to do so...
- Using the Format Painter
Copying you first cell by dragging it and then clicking the contextual menu ->Fill formatting Only
See here for more information about it.
And you are all set!
Best Answer
1) Verify that both your date fields are in a valid date format and not text, which would not work. Easiest way is to right-click on them, "Format..." and change the format to Number. You should see the corresponding number like 40123 or similar.
Also, you can test it with the formula =D1>B1 on a blank cell, which should return TRUE if it's greater.
2) Select a row, say row 10, and create a new conditional formatting rule with this formula (the $ sign tells the rule to always check columns B and D specifically, since in the background the rule will do the calculation on each column indivually)
Save, check if your row is highlighted. If it works, extend the area of your rule ("Applies to") to the whole sheet or desired row.
P.S. You don't need to start with a row then extend the area, I just prefer this way for testing it as I go. If you make your rule with multiple rows selected, just make sure that the formula you type uses the row number of the FIRST row in your selection.