CSV Dates Not Sorting

[Chris Thornburg]

The CSV column that is in issue is filled with dates and nulls. It is an
exported list from a software program we use.
When you open the CSV file with Excel and it automatically converts, it sets
the date fields type as General. Even after you change the field to a date
from General, it still does not reconginize it as a date field. It gets
!value errors when using =Year() and it does not sort by date it sorts
alphanumerically.

Current Workaround:
If you change the filename extension to XLS so it doesn't auto convert the
CSV, you have the option to do the "text to columns" button. With this, you
can specify each column and mark the date columns as dates. When the
conversion completes you can use Year() and sort by date properly.

Is there any easier way to fix this problem so I don't have to teach people
how to use "text to columns"? Ideally, I want them to be able to just click
on the CSV file and not have them do anything technical.

Thanks,

 

[Pete_UK]

If the "dates" look like dates, then try this:

Click on an empty cell somewhere and click <copy>. Then highlight the
offending "date" cells and Edit | Paste Special | Values (check) | Add
(check) | OK then <Esc>. If you get numbers like 39456, then you will
need to format the cells as dates.

Hope this helps.

Pete

 

[Earl Kiosterud]

Chris,

If the dates look like dates with General formatting, then they're not true dates, and
changing the formatting to date won't do any good. I suspect that the date format in the
CSV file isn't one Excel recognizes as a date, and so it's being put in the cells as text,
with General formatting. Give some examples, and we can probably write you a formula for an
another column that will change your text dates to real dates. Then they'll sort as dates.

 

[Chris Thornburg]

http://download.yousendit.com/0F79C8C04258E709

This is the example I am using. It seems like Excel would be able to figure
out this column is a Date column on the auto convert instead of making it a
General. I do know how to make a formula in the next column to correct this.
However this is not for me. This is for a simple non technical user. This
seems like something that shouldn't require effort on the end users part
besides possibly changing the column format. Thank you for replying and the
help.
PS: I am currently using Excel 2007 for those trying at home.

 

[Earl Kiosterud]

Chris,

I've looked at your CSV file. It's the space before the date that's causing Excel to not
recognize it as a date and convert it. If you can get your application to leave out that
space, it'll work. If not, try this in a helper column:

=VALUE(D2).

Copy down by dragging the Fill Handle. You'll need to format the column for the date you
want (Format - Cells - Number - Date).

You can select and Copy this column, then select the column with the dates (D), and Edit -
Paste Special - Values) and then your original date column will be replaced with the fixed
dates. Then you can remove the helper column.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------

 

[Billy Liddel]

Chris

Another way, if you don't mind macros. Copy the code into a VB Module - Alt
+ F8, Insert Module, paste this code

Sub RemoveSpace()
For Each c In Selection
c.Value = Trim(c)
Next c
End Sub

Return to file, select the data columns D & E then run the MAcro Alt + F8.

As you will probably be doing this often, it is a good idea to save the code
in your personal Excel file, then it will always be available.

Peter