Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
switched date format opening dbf file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
switched date format opening dbf file
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 ?B?YWRhbUB0d3Yub3Jn?= wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date format problem from opening dbf file in excel 2007 | Excel Discussion (Misc queries) | |||
How to use the exact format in a csv file when opening a csv file | Excel Discussion (Misc queries) | |||
When opening-file format is not valid | Setting up and Configuration of Excel | |||
Opening a csv file with US date format on a Australian PC | Excel Discussion (Misc queries) | |||
Date format when opening a .CSV file programmatically | Excel Programming |