Forum Discussion

filenko30's avatar
filenko30
Copper Contributor
Apr 26, 2024

Conditional Formatting Data/Time with XLOOKUP

Hello,

 

I have two columns, one of which contains a date and time in a "M/D/YY h:mm AM/PM" format, and another that contains the same, but derived via XLOOKUP. I want to enable a conditional formatting function that would look at the two columns and fill in red when the date or time do not match. For example:

 

1/1/23 12:00 PM1/1/23 12:00 PM
1/1/23 12:00 PM2/1/23 12:00 PM
1/1/23 12:00 PM1/1/23 12:30 PM
1/1/23 12:00 PM1/1/23 12:00 AM

 

I have tried "=$A1<>$B1", but it highlights every cell red, I suppose because the second column is derived via XLOOKUP which makes it always non-identical to the first. Is there a function that can compare the values of the cells as seen?

 

Link to a sample workbook: https://docs.google.com/spreadsheets/d/1O94EXvDDpQC2reQl0tJkMdhUjkUk4Rz-/edit?usp=sharing&ouid=104073511558277365577&rtpof=true&sd=true

Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 26, 2024

    filenko30 

    Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 26, 2024

        filenko30 

        Thanks.

        Columns E/F contain date+time values.

        The formulas in columns I and J use the TEXT function, so they return text values. Simply remove TEXT from the formulas:

         

        =IF(XLOOKUP(A2,Sheet1!A:A,Sheet1!E:E,"")="","",XLOOKUP(A2,Sheet1!A:A,Sheet1!E:E,""))

         

        See the attached version.

Resources