This works for me although it demand a few steps:
1. Select the column with the US dates, format as Aussie dates, make sure
the column to the right is empty, do datatext to columns, select delimited
click next, select space as delimiter click next, under column data format
select MDY (you need to select the original date format) and click finish.
This will give you a column with correct dates and a column with the times,
to get back everything together use a third column, assume the original
column was A and the created time column is B, in C put
=A2+B2
Copy down and format as dd/mm/yy hh:mm
finally copy and paste special as values the so the third column is
independent of A and B
delete A and B
you might run into problem with dates that can be interpreted like 1/2/2006
which would be Feb 1st in Australia and January 2nd in the US, that can be
fixed if you always make sure the import is coming in as text..
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"altona" wrote in
message ...
Hi there,
I have to regularly download data from a US site and I need to reformat
the dates, does anyone know how to change the formats below: from
mm/dd/yyyy h:mm to dd/mm/yyyy h:mm
English (US) ................English (Australian)
01/15/2006 05:00 ................15/01/2006 5:00
01/15/2006 05:20 ................15/01/2006 5:20
01/15/2006 05:40 ................15/01/2006 5:40
01/15/2006 06:00 ................15/01/2006 6:00
01/15/2006 06:20 ................15/01/2006 6:20
01/15/2006 06:40 ................15/01/2006 6:40
01/15/2006 07:00 ................15/01/2006 7:00
I have tried to highlight the data then (Format-Cells-Custom) but the
mm/dd does not change. At the moment I start a new column with the new
format and use the click and drag, however the data is over several
months and every day or so it misses some time and then I have to
change the dates in two cells to start over again. Hope I am making
sense here?
Regards ................ Bill
--
altona
------------------------------------------------------------------------
altona's Profile:
http://www.excelforum.com/member.php...o&userid=34139
View this thread: http://www.excelforum.com/showthread...hreadid=539096