THere are some custom functions available from briandunnings.com - but it might be easier to separate the values into a list:
YourList = substitute(YourDateTime;" ";"FileMaker_CarriageReturn)
YOu can get the individual items with GetValue(YourList;2) for the second value (25) and so on. You could build a custom function that creates the desired output-format
[…] Sometimes (CDT is there sometimes not for example) Ore Mon is written monday.
We don't know what data we get with the import. […]
I think you can ignore both the CDT part (an add-on to the numerical time zone difference) and the weekday name (can be calculated from the date).
Here's a calculation that parses out the desired parts based on your sample; adjust as need for any variations.
Let ( [
input = "Mon, 25 Apr 2014 07:11:00 -0500 (CDT)" ;
inputList = Substitute ( input ; [ " " ; ¶ ] ) ; // returns "Mon,¶25¶Apr¶2014¶07:11:00¶-0500¶(CDT)"
dateStringList = MiddleValues ( inputList ; 2 ; 3 ) ; // returns "25¶Apr¶2014¶"
theMonthName = GetValue ( dateStringList ; 2 ) & ¶ ;
monthNameList = "Jan¶Feb¶Mar¶Apr¶May¶Jun¶Jul¶Aug¶Sep¶Oct¶Nov¶Dec¶" ;
theMonth = ValueCount ( Left ( monthNameList ; Position ( monthNameList ; theMonthName ; 1 ; 1 ) ) ) ;
theDate = Date ( theMonth ; GetValue ( dateStringList ; 1 ) ; GetValue ( dateStringList ; 3 ) ) ;
theTime = GetAsTime ( GetValue ( inputList ; 5 ) ) ;
TZD = GetValue ( inputList ; 6 )
List ( theDate ; theTime ; TZD )
// for demo purposes; returns "8/25/2014¶7:11:00¶-0500" for the sample data
// if used in a script, create a Set Variable with this Let() where you define theDate, theTime, TZD as $vars and return a dummy result; then distribute the $vars via Set Field
We have a field that is imported with this information :
Mon, 25 Aug 2014 07:11:00 -0500 (CDT)
Now we want to make shure
… another fellow with a background in Sound-Engineering?
Shure use to be my favorite Microphones in Live-Environment
Thanks Erols for this calculation, it works like a charme.
But what about when a import field does not have the Mon, in the field than you get the wrong information.
How can i filter this out ?
Like first trim out 3 characters left from the , and the , it self and if it is not there than don't clean in out.
Trying to compensate for every possible input anomaly in the calculation is going to be near impossible.
The input domain (all the potential permutations of imported data going into the calculation ) has to be known in its entirety and the calculation needs to be built to function correctly every time.
Can it come on as
Mon,25 Apr 2014 07:11:00 -0500 (CDT)
Mon, 25 Apr 2014 07:11:00 -0500 (CDT)
Mon ,25 Apr 2014 07:11:00 -0500 (CDT)
Mon , 25 Apr 2014 07:11:00 -0500 (CDT)
Monday,25 Apr 2014 07:11:00 -0500 (CDT)
Monday, 25 Apr 2014 07:11:00 -0500 (CDT)
Monday ,25 Apr 2014 07:11:00 -0500 (CDT)
Monday , 25 Apr 2014 07:11:00 -0500 (CDT)
You get the picture...
Erolst gave you a great starting point and said "Here's a calculation that parses out the desired parts based on your sample; adjust as need for any variations."
Adjust as needed.
It is a great starting point, it is also working for i think 95% of the records until now.
The thing i run in to is, that some systems are not placing Mon, in front of the date. So then the date is not a middle value but a first value. Is it possible to make a clean up function that removes Mon, if it is there, and if it is not there there is no need to remove it.
I got it, substitute the version that are used. It is working now like a charm, thanks for helping me think this out. Not giving a answer but directions is good way to learn.