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