How can I convert US-format date and time into a date value so I can use the local date format?
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.