Forum Discussion

JeremyLahners's avatar
JeremyLahners
Qrew Cadet
3 years ago

Convert timestamp to Date/Time

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:
{"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
------------------------------
  • 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
    ------------------------------
  • 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
    ------------------------------
    • JeremyLahners's avatar
      JeremyLahners
      Qrew Cadet
      Thank you Prashant - worked perfectly!

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