converting a data string into time

[#1]

hi,

I am having trouble with trying to format my data into a time column. currently, my database returns and time value of ex: 0538AM meaning 5:38 AM. i am doing an analysis based on time and i need to convert my data into somthing that is usable, preferably in the form of hh:mm.

I have tried using the following functions: time, time#, num#, num, timestamp, and timestamp# to give me usable data but it only seems to return null or 12:00 AM values. an example of the code i have been trying is: time(num#(mid(time1,1,4))). when i use the code without the time function in front i get results like this: 0538.

Any suggestions?

Thanks so much

[swuehl]

I might have an idea what your problem was:

Have you used Time1 (the resulting number after string parsing) for time functions?

I think this will not work, time is encoded as decimal places after the whole number, e.g 09:00 am 0.375.

In this example, you parsed 900 into Time1 as a number, this results always to 12:00 AM, because there are no decimal places.

Stefan

[swuehl]

Sure, here you are:

=time(time#(TIME,'hhmmtt'))

[#4 · Accepted solution]

Hi,

So i ended up using a code similar to the one you suggested in addition to the information you gave me about how QV operates with its time function.
time#(time((num#(Left(TIME,4))/2400),'hhmmtt'),'HHMM')

Thanks for all of your help. I think this solves my problem.
Matt

[#5]

Some cases you have 105 to represent 1:05 AM without 0´s at the begining of the string.

In case this worked for me:

if(Len(TIME)=4 ,Time(Time#(TIME,'hhmm'),'hh:mm') ,  if(Len(TIME)=3 ,Time(Time#('0'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=2 ,Time(Time#('00'&TIME,'hhmm'),'hh:mm') , if(Len(TIME)=1 ,Time(Time#('000'&TIME,'hhmm'),'hh:mm') , 0 ) ) ) )

Hope this help