Forum Discussion
Conditional Formatting dates
This simply means that your dates are not real dates but the dates in text format.
Excel treats dates as real numbers and to check this enter a date in any cell and change it's format to General, the entered date (if a valid date) will be converted to the number underneath it.
The other way to check if a date in any cell is real date or a date as text, assume your date is in A2 then place the formula =ISNUMBER(A2) in any blank cell and if the formula returns True, that means the date in A2 is a real date otherwise it's just a text.
So if the dates in column A and B are not the real dates, you may select whole column A and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.
=AND(A1<>"",B1<>"",IF(ISNUMBER(A1),A1,DATE(20&RIGHT(A1,2),MID(A1,4,2),LEFT(A1,2)))>IF(ISNUMBER(B1),B1,DATE(20&RIGHT(B1,2),MID(B1,4,2),LEFT(B1,2))))
Please refer to attached with a conditional formatting applied to the column A.
If texts, it's more reliable and more simple to convert texts to dates - Text To Columns and set proper date format on third step of the wizard
I think, Dates as Text entries can be converted into the real dates using the Text to Columns feature if date has the default date separator in it i.e. if the default separator for the date in the regional settings is "/" and the text date uses the "." as date separator, it won't be converted into the real date.
It works
In black is the source, coloured is Text To Columns result (I use ISO date format)
