Date Format
Posted by Matt Schmitt on July 02, 2001 9:29 AM
I am manipulating a text file using excel. When I break it out into columns, the date colums is a number. 12101 would be Jan 21, 2001. I went to format and custom, to make the date appear as 01/21/01. Now I need to sort the date column in ascending order. The sort puts the dates in numberic order. 05/21/97 will appear after 01/21/01. If I change the format of the column to date, Excel recognises it as a serial number instead of the actual date. How can i sort this column to put it in the order I need?
Posted by Ben O. on July 02, 2001 9:56 AM
Instead of just making it look like 01/21/01, why not convert it to date/time format? You can do this with Text to Columns. On the third step you can specify Date and choose MDY as the format.
The only problem is that the width of your number is only 5 characters and Excel takes the leftmost 2 characters as the month, which in this case will be 12. To correct this, add a large number with a lot of trailing zeros to it, such as 100000000. Then, when you do Text to Columns, you can put a column divider before 012101 (you can choose to not import the first part of it) so it'll take 01 as the month rather than 12. I've had to do this trick to many spreadsheets my company has recieved.
-Ben
Posted by Eric on July 02, 2001 10:13 AM
=IF(A1>99999,(DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))),(DATE(RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2))))
Convert the values to real dates and the sort works fine.
I used
=IF(A1>99999,(DATE(RIGHT(A1,2),LEFT(A1,2),MID(A1,3,2))),(DATE(RIGHT(A1,2),LEFT(A1,1),MID(A1,2,2))))
to discriminate between 1 digit and two digit month values. Hope that helps
