7 Replies Latest reply on Jan 7, 2010 2:16 PM by HugoLidia

    American Dates to British date conversion issue



      American Dates to British date conversion issue


      Using Windows XP SP3 & FMP 10


      I know this is really not a FM issue, as problem lies in Excel 2007 spreadsheet, but is an import issue which I thought some kind soul might help with. 


      I have extracted data from an old system which has a date field in it, which displys in the old system as a British date i.e. DD/MM/YYYY but is actually stored in American date format i.e. MM/DD/YYYY.  Only realised when I imported data and saw that Age calculation field was not working on some records because of this problem.


      So I tried to convert the data in the excel spreadsheet by breaking down the dates into three columns and then reconstituting the date so ;

      19 of Jan 1964, held as :  01/19/1964(column a, row1)  became  19/(column B)    01/(column C)   1964(column D)     19/01/1964(column E) in four separate columns.  I thought this was fine except, some dates have produced very strange results e.g.

      02 of Oct 1972, held as : 10/02/1972  became   39     263    0980    392630980.  ( I have no idea what these values relate to)

      The formula used to extract the text for each of the four columns was ;

      =Mid(A1;4;3)       =Left(A1;3)     =Right(A1;4)      =Concatenate(B1+C1+D1)


      Can anyone offer a suggestion what excel is doing, or suggest a better way to convert or import these dates, held as text in old system.




      Update:  Upon further investigation there is a pattern to the spurious dates: see sample below

      08/01/1924        4877877448778774

      08/03/1926         4956956449569564

      07/01/1927       99869869998698691

      0/06/1927         231000023231000023


      The first column is displayed as a date but appears to be stored as a value.  In the first example as 8774  which when operated on using

      =Mid(A1;4;3)       =Left(A1;3)     =Right(A1;4)   yields  4   877  and 8774  -  but why?

      Form 3000 records, 421 have these strange results.