Converting imported data

  • Thread starter Thread starter Shawk
  • Start date Start date
S

Shawk

Hi all,

Apologies if this has been asked before - did a search of the messages but
didnt find the info.

I have a database of accidents. The entries in the date field are all in
the American format, i.e. 11/04/03. I need the UK format, 04/11/03 as my UK
PC doesnt recognise these as dates. Is there a quick way of converting this
column of over 1000 entries so that I can sort the data?

Be gentle - I'm no expert on Excel.

Many thanks, Shaun
 
Shawk said:
Hi all,

Apologies if this has been asked before - did a search of the messages but
didnt find the info.

I have a database of accidents. The entries in the date field are all in
the American format, i.e. 11/04/03. I need the UK format, 04/11/03 as my UK
PC doesnt recognise these as dates. Is there a quick way of converting this
column of over 1000 entries so that I can sort the data?

Be gentle - I'm no expert on Excel.

Many thanks, Shaun
Click to expand...

PS and apologies - using Excel 97.
 
Just use the format command. The date can be shown as you
prefer provided the dates have been entered as dates and
not text. If the latter then you can use formulae to
convert.
 
Pat said:
Just use the format command. The date can be shown as you
prefer provided the dates have been entered as dates and
not text. If the latter then you can use formulae to
convert.
Click to expand...

Thanks Pat but I tried that 1st. The data is in mm/dd/yyyy format but my pc
only recognises dates in the dd/mm/yyyy format. The format command doesnt
work because of this. I either need to find a simple way of converting this
or set up my pc for American formats just for this one file which will be a
pain. Shaun
 
Hi

Are you sure they are in date format, or they are strings in date format. To
test it, enter a number 1 into some free cell, copy it, select a range with
dates, and then PasteSpecial.Multiply. When the result turns into number,
then format as your date format. When not, then you truely need to convert
it. The converting formula for date in A1 is
=DATE(VALUE(MID(A1,7,4)),VALUE(MID(A1,1,2)),VALUE(MID(A1,4,2)))
and format the result as date.
 
Arvi Laanemets said:
Hi

Are you sure they are in date format, or they are strings in date format. To
test it, enter a number 1 into some free cell, copy it, select a range with
dates, and then PasteSpecial.Multiply. When the result turns into number,
then format as your date format. When not, then you truely need to convert
it. The converting formula for date in A1 is
=DATE(VALUE(MID(A1,7,4)),VALUE(MID(A1,1,2)),VALUE(MID(A1,4,2)))
and format the result as date.


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets


my be
Click to expand...

Arvi,

I did say be gentle.....

If cell B2 has the date 11/28/02 and I want it to be 28/11/02 how does that
apply to your formula?

I have used paste/special and formatted as a date for the whole column.
Where the dd figure (in mm/dd/yyyy) is 12 or less this has been successful
and I can then format, custom dd/mm/yyyy as Kuri suggested. Where it is
over 12 I cannot do a thing with it.

Thanks for the interest and help, Shaun.
 
Hi


Shawk said:
format.

Arvi,

I did say be gentle.....

If cell B2 has the date 11/28/02 and I want it to be 28/11/02 how does that
apply to your formula?

I have used paste/special and formatted as a date for the whole column.
Where the dd figure (in mm/dd/yyyy) is 12 or less this has been successful
and I can then format, custom dd/mm/yyyy as Kuri suggested. Where it is
over 12 I cannot do a thing with it.
Click to expand...


When the date in B2 is 11/28/2002, the the formula returns the date
28.11.2002 in my comp, but I can format it as "dd/mm/yy" with display as
28/11/02 too.
When the date in B2 is 11/28/02, then the formula returns 28.11.1902 for me.
To get the correct answer with 2-digit year, the formula'll be somewhat like
=DATE(VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<N;100,0),VALUE(MID(A1,1,2)),V
ALUE(MID(A1,4,2)))
where N is the switching point between centuries, i.e. with N=10, the date
01/01/09 is converted to 01/01/2009, but 01/01/10 is converted to 01/01/1910
NB! When you use the formula, then you don't have to format the source
column at all - leave it as it is.

When the original dates are in format "m/d/yy", then the formula'll be more
complex - you have to use FIND() function to determine the placement of
right "/" in string as base for position and length parameters in MID().
 
Arvi Laanemets said:
Hi





When the date in B2 is 11/28/2002, the the formula returns the date
28.11.2002 in my comp, but I can format it as "dd/mm/yy" with display as
28/11/02 too.
When the date in B2 is 11/28/02, then the formula returns 28.11.1902 for me.
To get the correct answer with 2-digit year, the formula'll be somewhat like
=DATE(VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<N;100,0),VALUE(MID(A1,1,2)),V
ALUE(MID(A1,4,2)))
where N is the switching point between centuries, i.e. with N=10, the date
01/01/09 is converted to 01/01/2009, but 01/01/10 is converted to 01/01/1910
NB! When you use the formula, then you don't have to format the source
column at all - leave it as it is.

When the original dates are in format "m/d/yy", then the formula'll be more
complex - you have to use FIND() function to determine the placement of
right "/" in string as base for position and length parameters in MID().
Click to expand...

Arvi,

We have very differing ideas of 'gentle' :-) Many thanks for this. It will
take me some time to work through and understand each point so dont expect a
reply anytime soon. But thanks - really appreciate it.

Shaun.
 
Hi
Arvi,

We have very differing ideas of 'gentle' :-) Many thanks for this. It will
take me some time to work through and understand each point so dont expect a
reply anytime soon. But thanks - really appreciate it.
Click to expand...


The task has his own limits :-)

Btw. my formula was for case where cource cell was A1

I'm trying to explayn it little.

You have there the formula
=DATE(Year,Month,Day)
which returns the date (i.e. Integer) accordingly 3 parameter values. You
have to calculate these parameters from source date.
Let the source date be in A1, as in my formula, and it's 11/28/02

The year starts there from 7th position (when day and month are always
2-digit numbers). To get the year, use MID() fiunction
MID(SourceString,StartPosition,Length) returns from SourceString a part
starting from StartPosition, and Length long. So
=MID(A1,7,4)
returns "02"
You need a number, not string, so convert it using VALUE() function
=VALUE(MID(A1,7,4))
returns 2
The year number 2 in Excel is the year 1902. To get 2002 instead, add 100 to
it. But you don't want the year 99 to be converted to 2099 instead 1999. So
you check the year number value, and add 100 only then, when this value is
less than some turning point - p.e. 10, i.e. year 1910. NB! You can see,
that when the date in A1 has 4-digit year, then nothing is added.
So VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<10;100,0) returns 2002 as year
for your formula and for A1=11/28/02

With month and day values goes it in similar way, only it's simplier, as
they always are 2 characters long (when the format you did refer to was
right one). The month part starts from first position and is 2 characters
long
MID(A1,1,2) returns "11"
and
VALUE(MID(A1,1,2)) returns 11
The day part
MID(A1,4,2) returns "28"
and
VALUE(MID(A1,4,2)) returns 28

Implement the formulas for year, month and day into the one we started with
(DATE()), and you have what you want.
=DATE(VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<10;100,0),VALUE(MID(A1,1,2)),
VALUE(MID(A1,4,2)))
 
Arvi Laanemets said:
Hi
expect


The task has his own limits :-)

Btw. my formula was for case where cource cell was A1

I'm trying to explayn it little.

You have there the formula
=DATE(Year,Month,Day)
which returns the date (i.e. Integer) accordingly 3 parameter values. You
have to calculate these parameters from source date.
Let the source date be in A1, as in my formula, and it's 11/28/02

The year starts there from 7th position (when day and month are always
2-digit numbers). To get the year, use MID() fiunction
MID(SourceString,StartPosition,Length) returns from SourceString a part
starting from StartPosition, and Length long. So
=MID(A1,7,4)
returns "02"
You need a number, not string, so convert it using VALUE() function
=VALUE(MID(A1,7,4))
returns 2
The year number 2 in Excel is the year 1902. To get 2002 instead, add 100 to
it. But you don't want the year 99 to be converted to 2099 instead 1999. So
you check the year number value, and add 100 only then, when this value is
less than some turning point - p.e. 10, i.e. year 1910. NB! You can see,
that when the date in A1 has 4-digit year, then nothing is added.
So VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<10;100,0) returns 2002 as year
for your formula and for A1=11/28/02

With month and day values goes it in similar way, only it's simplier, as
they always are 2 characters long (when the format you did refer to was
right one). The month part starts from first position and is 2 characters
long
MID(A1,1,2) returns "11"
and
VALUE(MID(A1,1,2)) returns 11
The day part
MID(A1,4,2) returns "28"
and
VALUE(MID(A1,4,2)) returns 28

Implement the formulas for year, month and day into the one we started with
(DATE()), and you have what you want.
=DATE(VALUE(MID(A1,7,4))+IF(VALUE(MID(A1,7,4))<10;100,0),VALUE(MID(A1,1,2)),
VALUE(MID(A1,4,2)))
Click to expand...

Thanks for going to all that trouble - I'm sure I will be able to pick my
way through it. Shaun.
 
I think I'd try closing excel and changing my windows regional settings to USA
style dates.

Then reopen the workbook (so excel will see them as real dates), then with excel
open, change my windows regional settings back to the UK style.

In windows98, I get to this setting via:
Windows Start button|Settings|control panel|regional settings applet
Date Tab.
 
This thread is a perfect example of why bottom posting is ridiculous.
I just wore out the scroll wheel on my mouse !
 
Ragdyer

I'm with you on the bottom posting issue.

Gord

This thread is a perfect example of why bottom posting is ridiculous.
I just wore out the scroll wheel on my mouse !
Click to expand...

Gord Dibben XL2002
 
Yes, you're right ... BUT ... you'll notice ... you read MY remark with a
SINGLE click of your mouse !
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
 
Back
Top