Certain imported dates formatted as text - how to change?

Hi all

I've been playing around with a problem for a while and can't get to the bottom of it

I have a dataset imported from an online system which is providing dates in American format. However, some are formatted as text (even though they look like dates) and stubbornly resist being changed.

I've attached the csv and a screen grab showing the problematic cells. I've tried clearing all formatting, changing the format to date, "text to column" to try to force them into a date format but none work. Even when the format is changed to date the value stays as text.

Any ideas as to how this is possible and how to sort it out would be very welcome

Thank you!

Dave

This link should be able to help you.
https://support.office.com/en-sg/art...4-32a67ec0a680

HTH

Greg

Hi Greg

Sadly that hasn't helped. There's something actually preventing the entries in certain cells behaving how they should. Certain of the text formatted cells convert to serial numbers as expected when using =datevalue() - but others don't. There's nothing in the cell/forumla bar of affected cells to indicate there's anything wrong. See attached.

Any idea what could cause this?

All the best

Davetext-dates-2.PNG