Copy/Paste dates from CSV file - date format error?

[michael.beckinsale]

Hi All,

Using Excel 2003 & Windows XP, UK regional settings.

I am developing a solution that is very date dependent and requires a
csv file to be produced from an external source. Data from the csv
file is then copied & pasted into an Excel workbook. However it seems
that although the dates in the csv file are in UK format when they get
pasted into Excel they are in US format but only for days 1 to 12.

I seem to remember something from the dim & distance path that
Microsoft knew of this 'limitation'.

Can anybody

1) confirm this is a known error / limitation in XL2003
2) suggest a workaround
3) provide code for a workaround

All help gratefully appreciated.

Kind regards

Michael Beckinsale

 

[Dave Peterson]

#1. Rename your .csv file to .txt
Then use File|Open to open that .txt file.

You'll be prompted with a text to columns wizard. You can choose the format for
each field--including date in the dmy order you want.

#2. Change your windows short date format (control panel|regional settings...)
to match your data and then try importing the .csv. Then change the windows
short date format back to what you want.

ps. Those dates that got imported as dates weren't the dates you wanted.

01/02/2007 would bring in January 2, 2007 (using my USA settings). With dmy, it
would be February 1, 2007. So don't trust the accuracy of those dates when
using the .csv file with different windows short date formats.)

 

[michael.beckinsale]

Hi All,

Problem solved.(I think)

Trawled the newsgroup and confirmed that this is a known issue that
has not been addressed up to XL2003, l dont know about XL2007.

There appear to be plenty of suggestions for a workaround when
importing a file. Most of these revolve around re-saving the file &
importing as a text file or changing date values to date serial
values.

In my project l want to copy / paste values from a csv file using
VBA .To overcome the problem l have added code which formats the csv
file date column to text (ie shows the date serial value), then when
the data has been pasted into the target file l format the date column
as dd/mm/yy. I have tested this with several samples and all appears
to be OK. I have paid particular attention to days 1 to 12.

Can anybody see any potential risk with this strategy? This project is
very date intensive and l really want to avoid potential date errors
from the outset.

Regards

Michael beckinsale

 

[michael.beckinsale]

Hi Dave,

Thanks for your reply.

I have made a 2nd posting to this thread in which l describe the
workaround l have used which is pertinent to my project.

Would you be kind enough to read it and let me know if you can see any
risks / dangers?

TIA

Regards

Michael beckinsale

 

[Dave Peterson]

The only thing that might be a problem is if the workbook you're importing to is
set to use the 1904 date system.

If you're building the .csv file, maybe you could create a formula for that
field:

1234,"qwer qwer",=date(2007,12,25),"more stuff"

(untested!)

As long as you stay in the English language!

And if that formula string is brought in as text, you could
select that range
edit|replace
what: =
with: =
replace all

 

[Dave Peterson]

And maybe you could create a dedicated macro that would ask for the name of the
..csv file, copy it to a .txt file and do all the importing and parsing against
that copied file.

 

[michael.beckinsale]

Hi Dave,

Sorry for the delay in replying to your posts.

Thanks for taking the time to review my solution. I note your comments
re 1904 date system, l wouldn't have thought of that. The csv files
are generated by a internet banking system so l dont think it will be
an issue but it needs checking out.

With regard to your 2nd post the user selects the file to post from
the dialog box shown by 'GetOpenFilename'. VBA code is then used to do
the data manipulation & copy/paste as per my solution.

Once again thanks for your input.

Regards

Michael beckinsale