Using date field in Excel

Hello - I’m having real issues using the date created field in the exported data from our Keap account.

The date created column for our 30k list seems to contain two different formats of data. One is recognised by Excel as a date - the other appears identical (save it seems to be from the 10th of each month) but Excel does not recognise it as a date and it won’t let me reformat, even using DATEVALUE.

It’s driving me nuts as we wish to do lots of analysis on list growth and I’m stuck at the first hurdle.

I’ve created an example export (see grab) - but can’t seem to add it to this post - happy to email it anyone who might be able to help.

I’d LOVE to know how to get around this, ie: how to format the fields so Excel can use them as a date.

I’ve worked out how to isolate the dodgy fields - but not how to convert them.

Any help would be hugely appreciated.!

Screenshot 2021-09-28 at 15.38.11|690x452