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 |