Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem | Excel Discussion (Misc queries) | |||
Date Math Problem | Excel Worksheet Functions | |||
Excel 2002 date formulas problem | Excel Worksheet Functions | |||
problem with formatting cell to date format | Excel Worksheet Functions | |||
Problem with Date format from VBA code | Excel Discussion (Misc queries) |