Importing the date field from xls and storing that in a proper Date type value like “11/09/2011 10:42:40 ” you have to defiene a function that convert the value“409873.4563” e.g Number type to Dtae type e.g “11/09/2011 10:42:40”.

  Suppose you have a xls sheet having a column “Date” that you have to import into file maker DB. The field type is number means the value displaying 09 Nov 2011 10:42 but internally it is storing like 11/09/2011 10:42:40 in xls sheet.While you are going to import this number type value , it will store like “409873.4563” in Filemaker Database.

The function is “ConvertTimestamp”

The function is “ConvertTimestamp”

Let ( [

$numericStartDate = 36892.0000115741 ; // This is the numeric value of “1/1/1 00:00:01”

$numericStartTime = .0000115741 ; // This is the numeric value of “00:00:01”

$numberOfDays = (GetAsNumber ( fieldValue ) – $numericStartDate) ;

$date = GetAsDate ( “1/1/1” ) + Int ( $numberOfDays ) ;

$time = GetAsTime ( “00:00:01” ) + ($numberOfDays – Int ($numberOfDays )) / $numericStartTime

] ;

GetAsTimestamp ( $date & ” ” & GetAsTime ( Hour ( GetAsTime ( $time ) ) & “:” & Minute ( GetAsTime ( $time ) ) & “:” & Round ( Seconds ( GetAsTime ( $time ) ) ; 0 ) ) )

)

Note: “field value” is the function parameter that takes the value from the Filemaker DB.

The field in Fm Databse must contain a function in its autoenter calculation to convert and store the Proper Date type value.

e.g

Let ([ $timestamp = ConvertTimestamp ( zz_Creation_TimeStamp_xm )];

If ($timestamp =”?”;zz_Creation_TimeStamp_xm; $timestamp))

Here I am elaborating the performane of the above function.

1:>First the function “ConvertTimestamp” calculates its value by taking the data from the xls sheet and store it in $timestamp variable .

2:> If the data is proper format(e.g Date type value) in xls sheet then ConvertTimestamp functions value become “?”.So the fields value replaces its original value as it is Date type data.

3:> If the data is not in proper format(e.g Not a Date type value) in xls sheet then ConvertTimestamp function converts the value to a