Dear Support Team,
I stumbled upon the problem, when getting data from a SAP system, that the date format might looks something like that: /Date(1551398400000)/
Is there a way to edit the xml file or to parse somehow with a component to the right format? Unfortunately, I have not found any formula functions to convert to the right format.
Hi, assuming what you are getting is a string
'/Date(1551398400000)/' you can do:
→ Split first at “(” and then at “)”, take the item from the resulting array that contains the timestamp
SPLIT(SPLIT("/Date(1551398400000)/", "(" ), ")" )
→ Convert timestamp string to number with
DATETIME() to convert timestamp to datetime, resulting formula:
DATETIME(NUMBER(SPLIT(SPLIT("/Date(1551398400000)/", "(" ), ")" )))
→ You can also use
FORMAT_DATETIME_LOCAL to present the date in a more readable format
Thank you! That solves my questions.