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
- ROI_NOIFAug 26, 2019Copper ContributorThis dos not work for dates. (format DD.MM.YY)
- Subodh_Tiwari_sktneerAug 26, 2019Silver Contributor
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.
- SergeiBaklanAug 26, 2019Diamond Contributor
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
- SergeiBaklanAug 26, 2019Diamond Contributor
ROI_NOIF , you may check attached file
- SergeiBaklanAug 26, 2019Diamond Contributor
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.