Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
#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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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 "michael.beckinsale" wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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" wrote: 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste dates from CSV file - date format error?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing date format in copy paste | Excel Discussion (Misc queries) | |||
Date copy, Paste error | Excel Discussion (Misc queries) | |||
Copy/Paste Special (Date Format) | Excel Discussion (Misc queries) | |||
Copy-Paste sheets - error in the dates | Excel Worksheet Functions | |||
copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming |