Skip to main content
Solved

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
------------------------------

Best answer by PrashantMaheshw

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
------------------------------
View original
Jeremy Lahners LeadBaller
This topic has been closed for replies.

Forum|alt.badge.img+11
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
------------------------------
Terms & ConditionsAccessibility statement