Forum Discussion

ROI_NOIF's avatar
ROI_NOIF
Copper Contributor
Aug 26, 2019

Conditional Formatting dates

Hello all, 

I have to columns of dates A & B. 

I want to color the cell in column A red if the date is later then the date in the cell in column B in the same row.

need to do this to all rows... 

Thanks

Roi

excel

11 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 26, 2019

    ROI_NOIF 

    Hi Roi,

     

    Assuming your data starts from A1, select column A and apply conditional formatting rule with the formula

    =$A1>$B1

     

    • ROI_NOIF's avatar
      ROI_NOIF
      Copper Contributor
      Aug 26, 2019
      This dos not work for dates. (format DD.MM.YY)
      • Subodh_Tiwari_sktneer's avatar
        Subodh_Tiwari_sktneer
        Silver Contributor
        Aug 26, 2019

        ROI_NOIF 

         

        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.

         

         

Resources