stupid dates!!!

[neeeel]

I am reading data in from a number of excel files, and putting it all in one file, and am having problems with dates. The format in the original files is dd/mm/yyyy hh:mm, the format in the destination file is the same, but when I read a cell with a date, eg 01/11/2008, it is getting read as 11/01/2008 before I even do anything with it. ie, I read in the value, and when i do debug,print, or msgbox, the value at runtime is 11/01/2008. This is really confusing me and there doesnt seem to be any way around it. format() doesnt help. Any ideas on whats going on here?

[Bob Phillips]

This is a common issue. How are reading the data?

[neeeel]

[VBA]
Set r = WB.Sheets(1).Range("D1", temp)
'
'
'
for each c in r
'
c.Offset(0, 1).Value)
'
'
[/VBA]

im setting a range from first to last cell in sheet, then reading the value in as an offset to the original range.

I have found that if I remove all formatting ( cell styles-> normal) then it reads in as a double(?) and from there I can convert it in vba to a date, trying to work out how to remove formatting from all the cells in the range now

[Paul_Hossler]

1. In the destination file, could you format the date cells as dd/mm/yyyy? It sounds like it a matter of how the date is being displayed.

2. Try using the .Text instead of the .Value. That should bering it over as displayed, and then you can manipulate it if you have to

Paul

[Bob Phillips]

[VBA]
Set r = WB.Sheets(1).Range("D1", temp)
'
'
'
for each c in r
'
c.Offset(0, 1).Value)
'
'
[/VBA]

im setting a range from first to last cell in sheet, then reading the value in as an offset to the original range.

I have found that if I remove all formatting ( cell styles-> normal) then it reads in as a double(?) and from there I can convert it in vba to a date, trying to work out how to remove formatting from all the cells in the range now

Just cast it as a date.

[neeeel]

ye ive tried that, but for some reason it casts as the wrong date, ie, mm/dd/yyyy instead of dd/mm/yyyyy

[Bob Phillips]

Casting a date doesn't format it, it just ensures it is stored as a date. You can format as you want.

[neeeel]

formatting doesnt work, for some reason, the date is getting copied incorrectly, so no matter how i format it, a date stored as 01/06/2010 ( where it should be 06/01/2010) is still going to give me the 1st June 2010

[neeeel]

to quote from excel help,

"CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings"

the dates in the original sheets are obviously stored in some weird format. Using day() and month() still returns the wrong date on the raw data, ie day 1 and month 6, instead of day 6 month 1. I really cant see a way around this, can anyone help?

[GTO]

In the source/original file, are you sure that Excel is reading it as a date and not text?

[neeeel]

I think i have solved this, the original files are CSV format, by saving the files as excel worksheets, I think I get the correct dates. Just converting all the files now