I am in Australia and use the default date formats here (dd/mm/yyyy). Unfortunately, BandCamp exports sales data using a US date format: "m/d/yy h:mmpm". For the most part, Numbers treats this as text since most of the values don't make sense as dates when reading in the local format.
Is there a way to convert this text to an actual date value in Numbers?
The only function I have found that seems close is DATEVALUE, but it interprets the source data as d/m/yy, and so returns an error except where the dates make sense reversed. e.g. it returns an error for 1/18/19, and interprets 2/4/19 as 2 April 2019, when the source data means 4 February 2019.