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
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.