Forum Discussion

sipra's avatar
sipra
Occasional Reader
Oct 10, 2025

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 containing 30 september 2025 changes when I format date column. I don't have issues with other columns. I tried custom formatting as well on date column but still the same few cells dont change at all. Can someone please help?

community
excel
office 365

1 Reply

  • SnowMan55's avatar
    SnowMan55
    Bronze Contributor
    Oct 10, 2025

    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.

Resources