AnsweredAssumed Answered

American Dates to British date conversion issue

Question asked by HugoLidia on Jan 4, 2010
Latest reply on Jan 7, 2010 by HugoLidia

Title

American Dates to British date conversion issue

Post

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.

Outcomes