4 Replies Latest reply on Dec 14, 2017 8:39 AM by Cheryl Morgan

    Changing Time to Military Time

    Cheryl Morgan

      Good morning,

       

      I have a string field for time. The time is in this format 8:15 PM or 8:15 AM. I need to group  the data by time frames (00:00-02:59). I need to do this for a 24 for hour period, or military time. All healthcare data in is based on military time.

       

      I have this code:

      IF Right([Collect Time2],2) = 'PM' then REPLACE([Collect Time2],LEFT([Collect Time2],1),'')

      ELSEIF RIGHT([Collect Time2],2) = 'AM' then REPLACE([Collect Time2],LEFT([Collect Time2],1),'0')

       

       

      ELSE ([Collect Time2])

      END

       

      and then my time frames are set to the following:

       

      IF     [Time Calculation] >= '00:00:00' AND [Time Calculation] <= '02:59:00' THEN '0000-0259'

      ELSEIF [Time Calculation] >= '03:00:00' AND [Time Calculation] <= '05:59:00' THEN '0300-0559'

      ELSEIF [Time Calculation] >= '06:00:00' AND [Time Calculation] <= '08:59:00' THEN '0600-0859'

      ELSEIF [Time Calculation] >= '09:00:00' AND [Time Calculation] <= '11:59:00' THEN '0900-1159'

      ELSEIF [Time Calculation] >= '12:00:00' AND [Time Calculation] <= '14:59:00' THEN '1200-1459'

      ELSEIF [Time Calculation] >= '15:00:00' AND [Time Calculation] <= '17:59:00' THEN '1500-1759'

      ELSEIF [Time Calculation] >= '18:00:00' AND [Time Calculation] <= '21:59:00' THEN '1800-2159'

      ELSEIF [Time Calculation] >= '22:00:00' AND [Time Calculation] <= '23:59:00' THEN '2200-2359'

      ELSE 'No Time'

      END

       

      Can you please help me get these times formatted correctly. I would really appreciate any help you can give. I know i am missing a major part in order to format this. But, i have looked at the data for awhile and cannot see how to fix it at this point.

      Cheryl