CSV Date Format and General Arrrggghhh-ness

[andyiain]

Hi and help.

I've got a csv file with lots of lovely information in it including
many a date field.

In the csv the date is in the format mm/dd/yyyy (or if there's a
leading zero m/dd/yyyy or mm/d/yyyy or m/d/yyyy), my problem is that
when I bring this in to excel it will recognise all of the dates with a
day of below 13 in the format dd/mm/yyyy and the other dates just go in
as text.

I can easily use a few text forumlea to reverse the day and month on
the ones recognised as text and these can happily be used as the
correct date but for the other dates they are taken as the wrong date
and I can't figure out a way or getting these back to being correct.

Any ideas?

 

[Dave Peterson]

I would rename the .csv file to .txt
then open that .txt file

You'll be able to specify the format for each field--including the date fields.

If you have to do this lots, you could record a macro when you do it once
manually and then just rerun that macro when you needed to import the .txt file
(important that the file is not named .csv.)

 

[andyiain]

That's got it.

Thanks for your help there.

Cheers
Andy