Date Format

[Guest]

I am located in Australia and my PC Regional Option is set as dd/mm/yyyy short date format. I receive reports from a US based system that generates an Excel spreadsheet output. This report has a number of date & time fields which are in mm/dd/yyyy hh:mm format.

Through my browser access to the system the report is generated in html and I save it as an .xls file. The resultant worksheet interprets the dates as dd/mm/yyyy so that all fields where dd < 12 the cell is in date format but with day and month reversed and where dd > 12 the cell does not recognise it as a valid date format.

I have not found a way of transposing the mm/dd/yyyy to dd/mm/yyyy that does not involve cell by cell delete and retype of the mm/dd numbers in the required order. Neither changing my Regional Option to US format or changing the cell format to US has the desired effect. I have also tried saving the report in html and copy the date to a preformatted US date cell but this does not work either.

Is there a function or formular that will resolve this problem?

 

[Norman Harker]

Hi Russell!

Various ways

Change your date system to US.
Import your data
Change the date setting to Australia.

Import all as text
Select the column
You can use Data > Text to Columns
Delimited
Next
Don't select any delimiter
Next
Check Date
Check MDY [Note this is the format you have imported and not the
format you want]
Next


Parse the text dates into a DATE function formula using LEFT, MID and
RIGHT functions.

--
Regards

Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)