Forum Discussion
Conditional Formatting dates
Hi Roi,
Assuming your data starts from A1, select column A and apply conditional formatting rule with the formula
=$A1>$B1
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.
ROI_NOIF , you may check attached file
Format doesn't matter, dates in Excel are sequential integers where 1 is 01 Jan, 1900. Another story if you have text representation of dates. You may check in any empty cell by =ISTEXT(A1). If it return false, you have dates, otherwise date. Other way, Ctrl+1 on A1 and apply General format to check if you see integer number.
