Stop Excel Rounding Dates
I frequently have to read .csv files into MS excel. These are textual
files, containing text fields, floating point numbers, integers and
times. The first item on each row tells me what kind of data is on the
row, and so when the files has been read into excel I can autofilter
on the first column to separate out whatever rows of data I want.
That's all very well, apart from the Dates which are interspersed
throughout the file (in predictable columns for a given data type, but
all over the place in practice as there are many data types) The
format of the Data is something like:
(yes, a uk date!) i.e. they are to a very high accuracy in time, which
If I could only get these dates into excel without rounding them, I
would work with them by converting them to floating point seconds
after midnight (I need the accuracy, but don't really care what day
they are). However when excel reads in the .csv file it rounds the
date to accuracy of 1000ths of a second, which is not high enough. I
have no control over the format of dates in the input file sadly.
The unsatisfactory fixes I have come up with so far a
a) I can rename the .csv file to .txt, read it in, and then do
text-columns setting the column type (of every column) to text. This
stops excel messing up the dates, but means that all the numbers in
the file become text, so I can't do any calculations on them.
b) I can rename the .csv file to text and then read it in as a
delimited file, with delimiters set to "," and ":" - then any time
gets split up and excel doesn't intepret it as a date. But the
alignment of the data with all the column headers (explaining the
contents of each column) is lost.
Is there a better way? The only idea I have so far is writing a
preprocessor in some language like perl which goes through the file
putting a space before any item that looks like a date. A single space
on the front will stop excel interpreting it as a date on input. But
I'd really like a way of doing this simply in excel, eg by somehow
overriding the excel behaviour on reading a date in a .csv file. Can