+ Reply to Thread
Results 1 to 5 of 5

DBF to Excel - date conversion problem

  1. #1
    Registered User
    Join Date
    03-23-2006
    Location
    UK
    Posts
    7

    DBF to Excel - date conversion problem

    I have a DBF (FoxPro) table with dates displayed as dd/mm/yyyy. The values are stored as yyyymmdd e.g. 20070312. When I open the file in Excel 2007, the dates are transcribed into US format (03/12/2007) - it should read 12/03/2007 as my locale is UK.

    For a date where the dd values are greater than 12 i.e. 20070313, the date conversion fails and the value in the cell is shown as text 20070313.

    There is nothing I can do in Excel (other than create a macro) to present the dates correctly - it appears that the DBF driver used by Excel only reads dates in US format.

    Does anyone have a solution for this problem which does not involve a macro in Excel?

    TIA

    dai50

  2. #2
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097
    Hi,

    To turn your text dates into proper dates then you could use this

    =TEXT(--LEFT(A1,8),"0000-00-00")+0 then format the cell to DATE so that 20070313 becomes 13/03/2007
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  3. #3
    Registered User
    Join Date
    03-23-2006
    Location
    UK
    Posts
    7
    Thanks for you response - but I need to correct the problem before the data gets into Excel - this process is called by my software so by the time the Excel file is generated its too late - I want the data to be processed correctly rather than fix data that has been imported. I think that the only solution here is to handle the problem programatically and ensure the date field is in a suitable format that Excel can process correctly. I think the problem is with the driver that opens the DBF file - it doesn't take into account the UK locale which has been set up in Excel.

  4. #4
    Registered User
    Join Date
    06-25-2008
    Posts
    6
    Hi,

    Your observation is correct.
    You can also refer to the following link for internationalization issues (tips for writing a locale independent code):
    http://www.bmsltd.co.uk/ExcelProgRef...rogRefCh22.htm

    Regards

  5. #5
    Registered User
    Join Date
    08-26-2009
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: DBF to Excel - date conversion problem

    Quote Originally Posted by oldchippy View Post
    Hi,

    To turn your text dates into proper dates then you could use this

    =TEXT(--LEFT(A1,8),"0000-00-00")+0 then format the cell to DATE so that 20070313 becomes 13/03/2007
    Hello Friend,

    Thank you very much for solving my problem, which was exactly this only.
    I wanted to convert xxxxxxxx into yyyy/mm/dd.

    R K Bhatnagar

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1