2 Replies Latest reply: Dec 2, 2015 9:20 PM by Tamil arasu RSS

    Convert a string to Date & Time

    Jeremy Schmitt

      I was wondering, what would be the easiest way to convert this string into a date and time with the AM/PM on it? I don't know who though it was a good idea to output an '@' in the middle of this. Right now I am doing a 'left' and taking out the date part and converting, but I am having issues removing the time and formatting. There must be an easier way...



        • Re: Convert a string to Date & Time
          Stefan Wühl

          Just use the timestamp interpretation function Timestamp#() with an appropriate format code:


          =Timestamp(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT'))


          edit: If you want to separate date and time, just use the timestamp returned:


          =Date(Floor(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))


          =Time(Frac(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

          • Re: Convert a string to Date & Time
            Tamil arasu

            Another solution could be,


            =Date(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm TT))


            =Time(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm tt'),'hh:mm TT')


            You can also use PurgeChar syntax to remove the @ symbol form your string field.