SAP Date Format Conversion

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.

Regards,
Markus

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)/", "(" )[1], ")" )[0]

ā†’ Convert timestamp string to number with NUMBER()

ā†’ Use DATETIME() to convert timestamp to datetime, resulting formula:

DATETIME(NUMBER(SPLIT(SPLIT("/Date(1551398400000)/", "(" )[1], ")" )[0]))

ā†’ You can also use FORMAT_DATETIME_LOCAL to present the date in a more readable format

2 Likes

Thank you! That solves my questions.

1 Like

Hi Mari,

Have a follow up question on this. After the conversation to DATETIME i did ADD DURATION operation and now the format is in ā€œ2022-11-18T09:56:12.123Zā€ format and I need to change it back to ā€œ/Date(1551398400000)/ā€ Format to send this data in C4C for record creation.
Not able to find any function to convert it back

Thanks for the help!!