Date Problem
 

I'm new to excel and self-taught, so bear with me. I've imported a text file
of approximately 250 records into excel 2003 with one of the fields being a
date in the format dd/mm/yyyy - the year ranges from 1600 to 2005 so it needs
to be four digits. What is the simplest way to get the field into a
structure where I can sort the records into date sequence?

Hi

Excel can not deal with dates earlier than 1900 without addins. See
http://j-walk.com/ss/excel/files/xdate.htm
for one.

So you may have some real dates and some date-lookalike text in your file.
Those will cause problems. An indicator is that text left align while
numbers and dates right align in unfornatted cells.

If you however have all text that look like dates, you should be able to
create yyyy/mm/dd from it using LEFT, MID and RIGHT functions. That will
sort well.

HTH. Best wishes Harald

"Dutyman" skrev i melding
...
I'm new to excel and self-taught, so bear with me. I've imported a text
file
of approximately 250 records into excel 2003 with one of the fields being
a
date in the format dd/mm/yyyy - the year ranges from 1600 to 2005 so it
needs
to be four digits. What is the simplest way to get the field into a
structure where I can sort the records into date sequence?

Thank you for both the pre-1900 pointer and the solution to my problem. Did
as you suggested and it worked perfectly - I just need to start thinking
logically about problems!!

Regards

Bill

"Harald Staff" wrote:

Hi

Excel can not deal with dates earlier than 1900 without addins. See
http://j-walk.com/ss/excel/files/xdate.htm
for one.

So you may have some real dates and some date-lookalike text in your file.
Those will cause problems. An indicator is that text left align while
numbers and dates right align in unfornatted cells.

If you however have all text that look like dates, you should be able to
create yyyy/mm/dd from it using LEFT, MID and RIGHT functions. That will
sort well.

HTH. Best wishes Harald

"Dutyman" skrev i melding
...
I'm new to excel and self-taught, so bear with me. I've imported a text
file
of approximately 250 records into excel 2003 with one of the fields being
a
date in the format dd/mm/yyyy - the year ranges from 1600 to 2005 so it
needs
to be four digits. What is the simplest way to get the field into a
structure where I can sort the records into date sequence?

Glad to hear that Bill. Thank you for the feedback.

Best wishes Harald

"Dutyman" skrev i melding
...
Thank you for both the pre-1900 pointer and the solution to my problem.
Did
as you suggested and it worked perfectly - I just need to start thinking
logically about problems!!

Regards

Bill

"Harald Staff" wrote:

Hi

Excel can not deal with dates earlier than 1900 without addins. See
http://j-walk.com/ss/excel/files/xdate.htm
for one.

So you may have some real dates and some date-lookalike text in your
file.
Those will cause problems. An indicator is that text left align while
numbers and dates right align in unfornatted cells.

If you however have all text that look like dates, you should be able to
create yyyy/mm/dd from it using LEFT, MID and RIGHT functions. That will
sort well.

HTH. Best wishes Harald

"Dutyman" skrev i melding
...
I'm new to excel and self-taught, so bear with me. I've imported a
text
file
of approximately 250 records into excel 2003 with one of the fields
being
a
date in the format dd/mm/yyyy - the year ranges from 1600 to 2005 so
it
needs
to be four digits. What is the simplest way to get the field into a
structure where I can sort the records into date sequence?