Epoch Time to timestamp conversion

[SK28]

Hi,

I have a data as shown in the below,
format I want to convert into both date & timestamp format 

Some data points are in Date format and some in Date, Can you help me with this

Date

/Date(1690848000000)/

/Date(1690934400000)/

/Date(1691020800000)/

/Date(1691020800000)/

/Date(1691107200000)/

/Date(1691193600000)/

/Date(1691280000000)/

/Date(1691366400000)/

/Date(1691452800000)/

/Date(1691539200000)/

/Date(1691625600000)/

/Date(1691712000000)/

/Date(1691798400000)/

/Date(1691884800000)/

/Date(1691971200000)/

/Date(1692057600000)/

/Date(1692144000000)/

/Date(1692230400000)/

/Date(1692316800000)/

/Date(1692489600000)/

/Date(1692489600000)/

/Date(1692576000000)/

/Date(1693180800000)/

/Date(1693267200000)/

/Date(1693353600000)/

/Date(1693440000000)/

/Date(1693526400000)/

/Date(1693612800000)/

/Date(1693699200000)/

/Date(1693785600000)/

/Date(1693872000000)/

/Date(1693872000000)/

/Date(1693958400000)/

/Date(1694044800000)/

/Date(1694131200000)/

/Date(1694217600000)/

/Date(1694304000000)/

/Date(1694390400000)/

/Date(1694476800000)/

/Date(1694563200000)/

/Date(1694649600000)/

/Date(1694736000000)/

/Date(1694822400000)/

/Date(1694908800000)/

/Date(1694995200000)/

/Date(1695081600000)/

/Date(1695168000000)/

/Date(1695254400000)/

/Date(1695340800000)/

/Date(1695600000000)/

/Date(1695686400000)/

/Date(1695772800000)/

/Date(1695859200000)/

/Date(1695945600000)/

/Date(1696032000000)/

/Date(1696118400000)/

/Date(1696204800000)/

/Date(1696291200000)/

/Date(1696377600000)/

/Date(1696464000000)/

/Date(1696550400000)/

/Date(1696636800000)/

/Date(1696723200000)/

/Date(1696809600000)/

/Date(1696896000000)/

/Date(1696982400000)/

/Date(1697068800000)/

/Date(1697068800000)/

/Date(1697155200000)/

/Date(1697241600000)/

/Date(1697328000000)/

/Date(1697328000000)/

/Date(1697414400000)/

/Date(1697414400000)/

/Date(1697500800000)/

/Date(1697500800000)/

/Date(1697587200000)/

/Date(1697587200000)/

/Date(1697673600000)/

/Date(1697673600000)/

/Date(1697760000000)/

2/24/2023

2/27/2023

2/28/2023

3/1/2023

3/2/2023

3/31/2023

4/3/2023

4/4/2023

4/5/2023

4/6/2023

4/7/2023

4/10/2023

4/11/2023

4/12/2023

4/13/2023

4/14/2023

4/17/2023

4/18/2023

4/19/2023

4/20/2023

4/21/2023

4/24/2023

4/25/2023

4/26/2023

4/27/2023

4/28/2023

5/1/2023

5/2/2023

5/3/2023

5/4/2023

5/5/2023

5/8/2023

5/9/2023

5/10/2023

5/11/2023

5/12/2023

5/15/2023

5/16/2023

5/17/2023

5/18/2023

5/19/2023

5/22/2023

5/23/2023

5/24/2023

5/25/2023

5/26/2023

5/29/2023

5/30/2023

5/31/2023

6/1/2023

6/2/2023

6/5/2023

6/6/2023

6/7/2023

6/8/2023

6/9/2023

6/12/2023

6/13/2023

6/14/2023

6/15/2023

6/19/2023

6/20/2023

6/21/2023

6/22/2023

6/23/2023

6/26/2023

6/27/2023

6/28/2023

6/29/2023

6/30/2023

7/3/2023

7/4/2023

7/5/2023

7/6/2023

7/7/2023

7/10/2023

7/11/2023

7/12/2023

7/13/2023

7/14/2023

7/17/2023

7/18/2023

7/19/2023

7/20/2023

7/21/2023

7/24/2023

7/25/2023

7/26/2023

7/27/2023

/Date(1690848000000)/ has be converted in Date format
and these type of data points like 7/27/2023 shouldn't be effected it should show as 7/27/2023

Can you please help me on this

[brunobertels · Accepted solution]

Hi 

here is the formula to convert epoch time 

timeStamp(Makedate(1970,1,1)+ left(1679641200000,10) /24/60/60 + Date((120/24/60) , 'YYYY-MM-DD hh:mm:ss'))

now you need to adapt it to your field format like this :

timeStamp(Makedate(1970,1,1)+ left(KeepChar('/Date(1690848000000)/','0123456789',),10) /24/60/60 + Date((120/24/60) , 'YYYY-MM-DD hh:mm:ss'))

and add a if condition to identify rather epoch format or date format : 

If(left(YourField,1) = '/' , 

timeStamp(Makedate(1970,1,1)+ left(KeepChar( YourField ,'0123456789',),10) /24/60/60 + Date((120/24/60) , 'DD/MM/YYYY  hh:mm:ss')) , 

timestamp#(YourField ,'DD/MM/YYYY  hh:mm:ss' ) 

)

Hope it helps