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

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

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 repeats
this every 12 hours ?
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

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


"altona" wrote in
message ...

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 repeats
this every 12 hours ?
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