switched date format opening dbf file

[Guest]

I have a .dbf file (tab delimited) which opens in excel. The start looks like this:

V900000/3 Deane-Jacobs (test), Ruth 12/06/2004 6:08:00 PM 5
V900000/3 Deane-Jacobs (test), Ruth 12/06/2004 6:10:00 PM 9
V900000/3 Deane-Jacobs (test), Ruth 18/06/2004 6:43:00 PM 5
V900000/3 Deane-Jacobs (test), Ruth 22/06/2004 10:45:00 AM 14
There is a problem with dates. All the computer settings are for uk culture. Excel switches some of the dates transposing the day and month if they are both less than or equal to 12, when it also removes the time.

How do i fix this? if it did it consistently on every row I could just set a dateformat for this column after it has opened, but why does it switch some and not others?

Adam

 

[keepITcool]

hmm.. a dbf file is supposed to be for DbaseIII data.

i suggest renaming it to .txt so not to confuse excel.

then if you have xl2002 or above:

you can indicate the dateorder
in the textfile (or text to columns) wizard

it's select DMY sequence (3rd page of the wizard.)

you HAVE to do it prior to inserting, else you get a hodgepodge.
(some dates will be swapped others not..

a date of 2/2/2004 is ambiguous
a date of 2/18/2004 is not, since there are no 18 months...




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >