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?
 
Jackie ManConnect With a Mentor Commented:
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
 
Asim NazirCommented:
0
 
Jackie ManCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
jlfourieAuthor Commented:
As stated, I've already done this in the regional settings.
0
 
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 ManCommented:
You choose the right date format and the date format reverts to mm/dd/yyyy?
0
 
Jackie ManCommented:
Is your Windows Vista joined to a domain?
0
 
Jackie ManCommented:
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
 
jlfourieAuthor Commented:
Hi Jackieman
Attached is portion of data.
Regards
JL
 
Test-Data.TXT
0
 
Jackie ManCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.