Retaining date format from CSV

  • Thread starter Thread starter Richard Wilde
  • Start date Start date
R

Richard Wilde

I am generating an excel file from another application. One of the columns
is a date. However when I open up the spreadsheet the data is displayed as

04:43:0

when I double click it the date is displayed as

16/10/2004 14:04:43

How can I force the column to be read as a date column?

I know that I can select the column and format it as date/time but this is a
bit of a pain!

Can anyone help?
 
CSV, by definition, contains no inherent formatting information, so how
XL displays the value depends on your default date/time format.

To make things less of a pain, you could, among other things (a) record
a macro of applying your format, and attach it to a custom toolbar
button or keyboard shortcut, (b) record a macro of your import (if the
file has the same name every time) and reformat, or (c) you could make
the macro a little more sophisticated and have it ask you which file you
wish to open, then import and format it. Post back if you need help.
 
Thanks for that, but I think I actually have found an answer. If you first
format the date as an ODBC cananical date then excel opens this column as a
date!

e.g.
2004-10-15 15:43:06
 
Just to add to JE's response.

When/if you create this macro to do the import, rename your .csv file to .txt
first.

Your recorded code will ignore those field settings when it sees the file is
..csv.
 
Back
Top