Another Date problem

[Paul]

I'm importing spreadsheet into Access using a routine
that process the data a line at a time so I can validate
each record. I've got one field that is a date field.
These spreadsheets come from many sources and are
inconsistent. When I look at two spreadsheets from
different sources the dates look the same and have the
same format 'yyyymmdd'. However, when I put the data into
an access table sometimes it goes in correctly and
sometimes it goes in with day and month reversed. Could
this be because the system these spreadsheets were
created on have different region settings and how can I
tell.

Any clues to this would be appreciated.
Paul

 

[Tom Ogilvy]

Look at the dateserial number stored in the cell to get the date.

Use the Value2 property of the range.

 

[Paul]

Tom,

I've looked at value2 and the number is correct for the
date that's in there. I've tried to put value2 in the
acces table and it still reverses the month and day. I'm
completely bewildered by this.

Paul

 

[Tom Ogilvy]

I don't know how access treats or stores dates, but you can convert the
serial to a date string in any format you want; so try using something that
can't be misinterpreted

format(cell.Value2,"mmm dd, yyyy")