Change date format from US to English (Australian)

[altona]

Hi there,
I have to regularly download data from a US site and I need to reforma
the dates, does anyone know how to change the formats below: fro
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 th
mm/dd does not change. At the moment I start a new column with the ne
format and use the click and drag, however the data is over severa
months and every day or so it misses some time and then I have t
change the dates in two cells to start over again. Hope I am makin
sense here?
Regards ................ Bil

 

[Peo Sjoblom]

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 data>text 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]

Thanks Peo,
however I tried your method and came up with the following hicup
02/01/2006 0:00 11:00:00 AM
02/01/2006 0:00 11:20:00 AM
02/01/2006 0:00 11:40:00 AM
02/01/2006 0:00 12:00:00 PM
02/01/2006 0:00 12:20:00 PM
02/01/2006 0:00 12:40:00 PM
02/01/2006 0:00 1:00:00 AM
02/01/2006 0:00 1:20:00 AM
02/01/2006 0:00 1:40:00 AM
02/01/2006 0:00 2:00:00 AM
it changes to PM but only for 3 rows then goes back to AM, it repeat
this every 12 hours ?
Regards ........... Bil

 

[Peo Sjoblom]

Do you have the AM/PM in the original import? If so use fixed width instead
of delimited when you split it and in step 2 make sure the line is between
the date and the time and if there is a line between the time values and the
AM/PM double click it to remove it, continue with the rest as earlier
If that doesn't work post back

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey