2 Replies Latest reply: Dec 2, 2015 9:20 PM by Tamil Nagaraj 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 Nagaraj

            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.