2 Replies Latest reply on Mar 20, 2018 8:08 AM by Taylor Floyd

    Converting and calculating a string to datetime

    Taylor Floyd

      I'm attempting to get a calculated field(lets call it STARTDATETIME) that gets the DATE element from one field and TIME from another and writes a new datetime but I am having issues coming up with the logic. Ideally it would convert METADATA2 from a string into a time format and subtract that time from ENDDATETIME(eg. STARTDATETIME for below would be 3/18/2018 11:58:20 PM). The data format available to me are as follows:

      ENDDATETIME
      METADATA2
      3/19/2018  12:00:13 AM
      Time on syste: 1 m 53 s

      I've written a calc field that only pulls '1m 53 s' from [METADATA2]: Mid([Metadata2],15) but I can't figure out how to convert it to a time element and then create the new backdated field STARTDATETIME. This would have to work for the following scenarios: METADATA2= ## s OR ## m ## s OR ## h ## m ## s. Any help would be appreciated!