1 Reply Latest reply on Jan 13, 2017 5:37 AM by Norbert Maijoor

    STRING TO DATE TIME PROBLEM :(

    Tiffany Muskopf

      Ok, so had a problem with converting string to a date time format.  Here is the original message:

       

      Can someone please help????  I have a piece of string that reads like this 01:15PM.  I tried the DATETIME(left(APPT TIME],5), and it is almost correct, but my output is now 1/1/1900 1:15:00 am.  I don't care about the date so much, as I am trying to calculate lag time in minutes doing a date diff.  That will work, but first I have to convert my appt times and arrival times from string to date time properly.  Can someone help with this one.  I need the output to read something like this 01:15:00 PM.  I need the am to = am and the pm to = pm.

       

      Thank you.

       

      Someone gave me some advice to use this calculation:

       

      (MAKETIME(IIF(RIGHT([Appt Time],2)="PM",12,0)+INT(LEFT([Appt Time],2)),INT(MID([Appt Time],4,2)),0))

       

      Although it mostly works.  It won't work for my 12:xx PM or AM appts.  It actually turns my 12:xxpm appts into 12:xx am appts and reverse.  If anyone can help, that would be great!

       

      ThATanks again!

        • 1. Re: STRING TO DATE TIME PROBLEM :(
          Norbert Maijoor

          Tiffany,

           

          I am not sure if I will the "beauty-contest" with my approach but find it below as reference and stored in attached workbook version 9.3

           

          My date-field is text:

           

          a. Hours: mid(str([date]),1,find(date,":")-1)

          b. Hours Digital: if right([date],2)="AM" then [Hours] elseif right([date],2)="PM" then str(int([Hours])+12) END

          c. Minutes Digital: mid(str([date]),find(date,":")+1,2)  

          d. Time notation: maketime(int([Hours Digital]),int([Minutes Digital]), 00)

          e. Time notation > right click > textmeu > Create > Custome Date > Detail: Hours