Date incorrectly imported

Hi
I get a CSV data extract, and date format is dd/mm/yyyy.  I import the data into an excel sheet, and this used to work well (on XP).
Now I use a VISTA machine, still with Exel 2007, and the format seems to revert to mm/dd/yyyy.  I've changed the regional settings so that it shows dd/mm/yyyy, but it still doesn't work.  Are there other places in VISTA where date format needs to be changed?
Any ideas?
Thanks
JL
jlfourieAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Asim NazirCommented:
0
Jackie ManIT ManagerCommented:
Do you just open the csv file or import the csv file using the Text Import Wizard?

If you use the Text Import Wizard, do you customize the date format to MDY? An example is shown in the link below.

http://www.computerprep.com/proddesc/Courseware_Sample/1423918177.PDF#page=104

0
jlfourieAuthor Commented:
As stated, I've already done this in the regional settings.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

jlfourieAuthor Commented:
Hi jackieman
Thanks for comment.  I don't use the text import wizard, but I do ensure that the date format is correct.  So I select DMY, and my regional settings reflect the same, and the data is also in that format.
It used to work previously...
I do the extracting from Excel.  So with Excel open, I open the CSV file, and then choose the right formats.
Regards
JL
0
jlfourieAuthor Commented:
Hi jackieman
Actually I do use the text import wizard, never actually looked at caption.  And in that, I choose the right date format.
Regards
JL
0
Jackie ManIT ManagerCommented:
You choose the right date format and the date format reverts to mm/dd/yyyy?
0
Jackie ManIT ManagerCommented:
Is your Windows Vista joined to a domain?
0
Jackie ManIT ManagerCommented:
Can you upload a sample of your csv file?
0
jlfourieAuthor Commented:
Thanks for the assistance.  Somehow the regional settings are not honoured when VBA code runs.  When I change the regional settings to MDY it all works.  When it's DMY it seems as if the MDY settings are still honoured.  
Maybe the best is just to write some code to change the regional settings to MDY just before code runs, and then back to DMY thereafter.
Do you know how to do that in normal VBA code?
0
David Johnson, CD, MVPOwnerCommented:
if you hover over the date what is shown.. you should be getting some invalid values for the date.. or is it just being displayed incorrectly..
0
jlfourieAuthor Commented:
The dates are still valid.  It's as if VBA has built in MDY syntax, so even though the dates are in DMY format, they get "corrupted", or rather changed when the code runs.  Any date where the day is over 12 gets it's format changed.
0
jlfourieAuthor Commented:
As I mentioned before, if I keep the format as MDY, it works.  But I don't want to change date format in regional settings the whole time.
Which is why I was wondering if I cannot build that conversion in my VBA code.  Not most elegant, but seems like that's all that will work.
0
Jackie ManIT ManagerCommented:
Yes. It is a bug.

See whether you can change the VBA code just like the one in the link below.

http://www.eggheadcafe.com/software/aspnet/30646890/vba-changes-my-date-format.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jlfourieAuthor Commented:
Hi Jackieman
Attached is portion of data.
Regards
JL
 
Test-Data.TXT
0
Jackie ManIT ManagerCommented:
I use text import wizard to import your text file and the date format is correct in dd/mm/yyyy and match with my regional and language settings which is in dd/mm/yyyy.

Please post back your VBA code.
0
jlfourieAuthor Commented:
What does this code do?

For each cell in range("b1:b1200")
if left (cell.value,1) = "S" then
nextrow = range("O65536").end(xlup).row + 1
cells(nextrow,15) = cell.offset(0, -1).value
cells(nextrow,16) = cell.offset(0, 0).value
end if
next cell

I can't see how it works?

Regarding importing data, that works fine.  The problem comes with the code.  As soon as the code runs, it seems to transpose M and D...


0
jlfourieAuthor Commented:
Please ignore my question re code above for time being.  I only saw the = CDate(cell.Offset(0, -1) bit now.
Will try and let you know if successful.
Regards
JL
0
jlfourieAuthor Commented:
I still think there is a bug in VISTA that doesn't allow date formats to be used properly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.