jlfourie
asked on
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
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
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
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
ASKER
As stated, I've already done this in the regional settings.
ASKER
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
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
ASKER
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
Actually I do use the text import wizard, never actually looked at caption. And in that, I choose the right date format.
Regards
JL
You choose the right date format and the date format reverts to mm/dd/yyyy?
Is your Windows Vista joined to a domain?
Can you upload a sample of your csv file?
ASKER
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?
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?
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..
ASKER
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Please post back your VBA code.
ASKER
What does this code do?
For each cell in range("b1:b1200")
if left (cell.value,1) = "S" then
nextrow = range("O65536").end(xlup).
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...
For each cell in range("b1:b1200")
if left (cell.value,1) = "S" then
nextrow = range("O65536").end(xlup).
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...
ASKER
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
Will try and let you know if successful.
Regards
JL
ASKER
I still think there is a bug in VISTA that doesn't allow date formats to be used properly.
Read above and it will fix it.