Forum Discussion
Few cells in Date column is no formating
The "Date" column (E)was pulled from another excel file using xlookup. When I Try to format Date column few cells don't change at all. The cells containing dd-mmm-yyyy don't change. The cells con...
The problem is the data in the Date column of Table3—some of it is stored as text data, even though to the eye, it appears to be a date value.
It would be best to "clean up" that Date column data, replacing those apparent dates with actual dates.
Alternatively, for the most part, you could have your lookup formula convert the XLOOKUP return value, such as this:
=DATEVALUE( XLOOKUP(D17,Table3[Consignment ID],Table3[Date]) )But some of the data will then cause a conversion error. Note that "30-Sep-202" is missing the last digit of the year.