How can I convert US-format date and time into a date value so I can use the local date format? 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.
Set the documents Language &amp; Region setting to &quot;Englisch | United States of America&quot; in the respective dialog (File menu &gt; Advanced &gt; Language &amp; Region).


Formatting options for date cells in the Format sidebar should have US formatting now.

Import/paste the date data.

After importing/pasting, set the Language &amp; Region setting (back) to &quot;English | Australia&quot;. The pasted/imported cells with &quot;US&quot; dates will retain US date formatting. All other cells have AUS date formatting, so using DATEVALUE on the US formatted cells &quot;converts&quot; them to AUS date formatting.



Hope this makes sense, not an English native speaker.