Excel Date Problem

[Armitage]

I have a csv file with a date & time column, where the dates are in the form YYYY-mm-dd HH:MM:SS, for example 2006-06-16 11:23:13

The problem is that excel doesn't interpret these cells as dates & times. If I manually enter a date in this format, it recognizes it as a date, but not when I read a whole column of them from a file. I've tried defining a custom date format, but it's not working.

Any ideas?

 

[Armitage]

Goddamn stupid MS crap :|

In my text file import, there's a single space before the date. Delete that space, and it gets intepreted properly. This occurs in both Office 2000 and Office 2003
So now I have to figure out how to strip that one space out of a few 10s of thousands of lines.

FWIW, OpenOffice inteprets it correctly - I'll probably just do it there.

 

[oog]

when you import the csv file, can you specify the delimiter to be ", " rather than just ","?

 

[Armitage]

Originally posted by: oog
when you import the csv file, can you specify the delimiter to be ", " rather than just ","?

I don't recall if it asked me about delimiters, or just opened it? Yea, you could probably use ', ' as the delimiter and merge delimiters.

I dunno - I just did it in OO.

 

[oog]

It only prompts you about delimiters if you rename the file to a .txt extension. It puts you through a text import wizard. Anyway, it sounds like you have the problem solved.

 

[petesamprs]

Try dividing or multiplying the whole column by 1, by typing a 1 somewhere, Ctrl+C on it, and then paste special on your entire column using the multiply option.

Often times you can convince excel you really have a date (not just text) by trying to multiply it by 1.