How to convert US-format date and time into a date value so I can use the local date format

numbers

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.

Best Answer

  1. Set the documents Language & Region setting to "Englisch | United States of America" in the respective dialog (File menu > Advanced > Language & Region).

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

  1. Import/paste the date data.

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

Hope this makes sense, not an English native speaker.

Related Question