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...  ...
excel
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.

       

       

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

        Subodh_Tiwari_sktneer 

        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

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

      ROI_NOIF 

      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.

Resources