Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
UK to US Date Format
1 view
Skip to first unread message
Ron E. Mason
May 29, 2003, 2:04:05 PM5/29/03
to
I have a CSV data file from the UK which has a date
column. The date is in the format of dd/mm/yy. If I sort
the spreadsheet by date it puts all of the days together
rather than the month's.
column. The date is in the format of dd/mm/yy. If I sort
the spreadsheet by date it puts all of the days together
rather than the month's.
Does anyone have a clever way of converting the date
column to the standard US format of mm/dd/yy?
Thanks in advance!
Mike Tomasura
May 29, 2003, 2:29:53 PM5/29/03
to
If the date is in this format this code will work.
21/02/03 or 01/12/03
dd/mm/yy
21/02/03 or 01/12/03
dd/mm/yy
For x = 1 To 10
temp = Mid(Range("b" & x), 1, 2)
newdate = Mid(Range("b" & x), 4, 3) & temp & Mid(Range("B" & x), 6, 3)
Range("B" & x) = newdate
Next x
this code will make 21/12/03 -> 12/21/03
"Ron E. Mason" <r.m...@geac.com> wrote in message
news:53ff01c3260c$b14b9710$a501...@phx.gbl...
Ron E. Mason
May 29, 2003, 2:54:49 PM5/29/03
to
I put the code into a macro, changed the "x = 1 to 10"
to "x = 2 to 4510" because this is how many lines I have.
I then made sure that the format was dd/mm/yy. Ran the
macro, reset the format to mm/dd/yy. Seemed to work but
further investigation showed from line 570 to 945 got
messed up, date is like "/201/003" There are similar
changes like that throughout the column. It appears that
about 30% actually converted the rest is lost.
to "x = 2 to 4510" because this is how many lines I have.
I then made sure that the format was dd/mm/yy. Ran the
macro, reset the format to mm/dd/yy. Seemed to work but
further investigation showed from line 570 to 945 got
messed up, date is like "/201/003" There are similar
changes like that throughout the column. It appears that
about 30% actually converted the rest is lost.
>.
>
Ron E. Mason
May 29, 2003, 3:39:35 PM5/29/03
to
I figured out the the dates that start with a single digit
do not have a leading 0, so those are the ones that got
messed up.>.
>
do not have a leading 0, so those are the ones that got
messed up.
>
Another Steve
May 29, 2003, 5:37:54 PM5/29/03
to
Ron,
Can't you just re-import the csv file to excel and specify the date column
format before hitting the finish button? If you specify DMY, it should
import and convert to your own system format by itself. That's what I do
with US files I open on my UK system.
Can't you just re-import the csv file to excel and specify the date column
format before hitting the finish button? If you specify DMY, it should
import and convert to your own system format by itself. That's what I do
with US files I open on my UK system.
"Ron E. Mason" <r.m...@geac.com> wrote in message
news:53ff01c3260c$b14b9710$a501...@phx.gbl...
Ron E. Mason
May 30, 2003, 2:20:34 PM5/30/03
to
I am using Excel 2000, there is no import option. You
simply open the CSV file as you would any other
spreadsheet it does the rest. It does not give you the
choice to select the data type. But you give me an idea,
maybe I could import it into Access, do what you suggest
then open it in excel.
simply open the CSV file as you would any other
spreadsheet it does the rest. It does not give you the
choice to select the data type. But you give me an idea,
maybe I could import it into Access, do what you suggest
then open it in excel.
>.
>
0 new messages