Discussions

 View Only
  • 1.  Convert timestamp to Date/Time

    Posted 08-12-2022 08:56
    Hi friends!

    I am trying to convert a timestamp over to a Date/Time field and am failing miserably.  I have a text string that am I am extracting elements from using multiple formula fields, one of which is the intended Date/Time field.  I have all the other text fields working just fine but can't get the formatting right on the Date/Time.

    A snippet of the text I receive is:
    2Barianne@leadballer.com">seth+arianne@leadballer.com</a>&quot;,&quot;leadsource&quot;:&quot;leadballer&quot;,&quot;time&quot;:&quot;2022-08-11T13:18:08+00:00&quot;,&quot;type&quot;:&quot;InvitationAccepted&quot;,&quot;firstName&quot;:&quot;Roberta&quot;,&quot;lastName&quot;:&quot;Avila&quot;,&quot;occupation&quot;:&quot;Global Total Rewards Director&quot;,&quot;publicIdentifier&quot;:&quot;https:\/\/<A HREF="http://www.linkedin.com\/in\/roberta-avila-5352614">www.linkedin.com\/in\/roberta-avila-5352<wbr></wbr>614</a>&quot;,&quot;fullName&quot;:&quot;Roberta Avila&quot;,&quot;picture&quot;:&quot;&quot;,&quot;company_1&quot;:&quot;[{\&quot;Company\&quot;:\&quot; \&quot;,\&quot;Location\&quot;:\&quot; \&quot;,\&quot;description\&quot;:\&quot; \&quot;,\&quot;industries\&quot;:\&quot; \&quot;,\&quot;url\&quot;:\&quot; \&quot;,\&quot;companyPageUrl\&quot;:\&quot; \&quot;,\&quot;headquarter\&quot;:\&quot; \&quot;}]&quot;,&quot;current_company&quot;:&quot;FARO Technologies&quot;,&quot;current_company_domain&quot;:&quot;<A HREF="http://www.faro.com">www.faro.com</a>&quot;,&quot;current_company_url&quot;:&quot;https:\/\/<A HREF="http://www.linkedin.com\/company\/faro-technologies">www.linkedin.com\/company\/faro-technolo<wbr></wbr>gies</a>&quot;,&quot;current_company_location&quot;:&quot;&quot;,&quot;current_step&quot;:&quot;&quot;,&quot;degree&quot;:&quot;2nd&quot;,&quot;companyPage&quot;:&quot;https:\/\/<A HREF="http://www.linkedin.com\/company\/faro-technologies">www.linkedin.com\/company\/faro-technolo<wbr></wbr>gies</a>&quot;,&quot;seq_name&quot;:&quot;Ari Aranda Manufacturing Campaign- INTERNAL&quot;,&quot;companydomain&quot;:&quot;<A HREF="http://www.faro.com">www.faro.com</a>&quot;,&quot;industry&quot;:&quot;Mechanical Or Industrial Engineering&quot;,&quot;subtype&quot;:&quot;&quot;,&quot;msg&quot;:&quot;&quot;,&quot;msg_from&quot;:&quot;&quot;,&quot;msg_sent&quot;:&quot;&quot;,&quot;phonenumber&quot;:&quot;&quot;,&quot;zap_comment&quot;:&quot;&quot;,&quot;response_after&quot;:&quot;&quot;,&quot;thread_url&quot;:&quot;&quot;,&quot;email&quot;:&quot;<a href="mailto:roberta.avila@rocketmail.com">roberta.avila@rocketmail.com</a>&quot;,&quot;company_name_1&quot;:&quot;FARO Technologies&quot;,&quot;company_from_1&quot;:null,&quot;company_size_1&quot;:null,&quot;company_description_1&quot;:&quot; &quot;,&quot;company_location_1&quot;:&quot; &quot;}" class="column-hybrid-table-report_1638391346747_5_369-6 condensed styledTableCell css-1dji5zt eagn1gn13" data-test-id="textFormatter" style="height: 56px; overflow: hidden; width: var(--table-report-column-cell-width-6); max-width: initial; text-align: left; font-weight: normal; color: inherit; white-space: normal; min-height: 22px; padding: 4px 6px 0px">{"owner":"frank@example.com","leadsource":"leadballer","time":"2022-08-11T13:18:08+00:00","type":"InvitationAccepted", ...

    I can easily get that converted to text via a Formula-Text field and the formula:
    var number start = Find([Original JSON], "\"time\":\"");
    var number end = Find([Original JSON], "\",\"type\"");
    var number startlength = Length("\"time\":\"");

    Mid([Original JSON], $start+$startlength,$end-$start-$startlength)

    However, when I change over to a Formula-DateTime field and try to use ToTimestamp or other type conversions, it fails and I get a blank result.  

    Any suggestions on how to convert "2022-08-11T13:18:08+00:00" to a Date/Time?

    ------------------------------
    Jeremy Lahners
    LeadBaller
    ------------------------------


  • 2.  RE: Convert timestamp to Date/Time
    Best Answer

    Posted 08-14-2022 23:23
    Edited by Jeremy Lahners 08-15-2022 08:41
    Hey Jeremy ,

    TimeStamp you posted is Zulu Time ,you can read about in on google , basically it's GMT time and we need to add our time difference to it . 

    var text utc="2022-08-11T13:18:08+00:00"; // Declare your field here 
    var date pdate = ToDate(Part($utc,1,"T"));   // Extracting Date

    var text pa = Part(Part($utc,2,"T"),1,"+"); // Extracting GMT Time 
    var TimeOfDay ptime = ToTimeOfDay($pa)+Hours(5)+Minutes(30);  // Adding Time difference 5hours 30 minutes is IST Time difference, you will add or subtract yours.

    ToTimestamp($pdate, $ptime)

    ------------------------------
    Prashant Maheshwari
    ------------------------------



  • 3.  RE: Convert timestamp to Date/Time

    Posted 08-15-2022 08:41
    Thank you Prashant - worked perfectly!

    ------------------------------
    Jeremy Lahners
    LeadBaller
    ------------------------------