Conditional formatting is not working for less than TODAY(), only for greater than

1
votes

Conditional formatting does not apply to dates less than TODAY(), only greater than TODAY().

I have an IF statement with two VLOOKUPS inside. One of them outputs "Not Registered" if the cell looked is empty. The other, just copy the data that is on the other spreadsheet.

=IF(VLOOKUP($A5,Table,@MATCH(D$3,Headings,0))=0,"Not 
Registered",VLOOKUP($A5,Table,@MATCH(D$3,Headings,0)))

Conditional formatting is set to:

cell values >= TODAY() are green cell values < TODAY() are red cell values = "Not Registered" are yellow

I suspect the conditional formatting is not reading properly the date. Every date cell is formatted as long date.

Table where date cells are output and conditional formatting settings

2
excelconditional-formatting
Try checking the "stop if true" option in the CF dialog. What exactly is in A3? – Tim Williams
it should be =today() instead of A3... I had a =today() in A3 cell just to check if it would work. I have played around with stop if true but no success – Alexandre Pazolini Dibbe
So, cell J13 should be red, right? In an empty cell try =J13<today() - does that return TRUE or FALSE? – teylyn
It returns false – Alexandre Pazolini Dibbe
That means that cell J13 is not a proper date. If it was, it would return TRUE. Check the data type. – teylyn

2 Answers

0
votes

Reason: Whenever the cell go past by the Less than today rule, computer will read that as zero so it will return true because zero is less than today.

Solution: On the Conditional Formatting Rules Manager,try to enable the Stop if true for the not registered rule.

0
votes

The problem comes up because A3 is empty.

enter image description here

Change the rules to use =Today() or populate A3 with the desired date.

enter image description here

Edit 1: If this is not working for you, check the data type of cells that should be red. If these are not real dates (but text), then they will never meet the condition. Make sure that all cells that look like dates actually contain dates (and not text).

If the lookup table stores the dates as text, then you can make the changes there, because the Vlookup will return the same data type.

Edit 2: Selecting a cell and changing its format will NOT convert text to a date. You can test if a date is really a date by changing its format to General. If this results in the cell showing a number, then it's a real date. But if the cell appearance does not change, the value is text and you need a different approach.

One option would be to use a helper column with a formula like =DateValue(A1). Copy the helper cells and paste them as values over the original cells, then format as date.

Or, put a zero into any cell, copy the cell, then select all cells that may or may not be dates and use Paste Special > tick "Add" > OK. That will convert dates stored as text back to their internal storage number. Then format the cells as dates.

Or, do the conversion after the Vlookup by wrapping a DateValue around the VLookup formula.

=DATEVALUE(VLOOKUP("a",A1:B1,2,0))

enter image description here

The "date" in B1 is really text. The Vlookup returns it as text, but the DateValue() then converts it to a date. If this one throws an error, the date text does not agree with your regional settings of what a date is expected to look like.