AnsweredAssumed Answered

Splitting European dates with unusual characters

Question asked by mz5005 on Jun 7, 2017
Latest reply on Jun 8, 2017 by mz5005

I need to split up from an external source table, a European date format with (also for Europeans) unusual dots like


(see also previous discussion step 1 )


Note that some date functions in FMP require that the system time needs to be in the same format as the data file to work properly.

Because changing back and forth system date formats regularly is not what I want, I split up the date in 3 text fields (goes well).

The problem is to stitch them back together to my yyyy-mm-dd format (date field). Tried with Date(), GetAsDate() etc but don't get it right. See screenshots.


Note that because these are files with over a million records and containing financial data and on top of that data is added regularly, I don't want a "whiz" formula to change the European date (actually a time-stamp) all at once, but want to do it step by step. Later I will write checking scripts to see if all the date (and time) conversions have gone well (using the year/month/day fields) given that with this file size a visual check is not enough.


Greatly appreciate your help!