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]

You can use time() function to format a time / date field.

But you still want to get one, so I think you should try:

time#(mid(time1,1,4),'hhmm')

or time#(time1,'hhmmtt')

Then you can format this to e.g. hh:mm format

time(time#(time1,'hhmmtt'),'hh:mm')

{hhmmtt should be the correct format code for your input, please refer to and check the format code page in the help}

Regards,

Stefan

[#3]

Hi Stefan

I have tried that but it still gives me the same problem were all the times returned are 12:00:00 AM.

Thanks for the suggestion

[nagaiank]

The solution given be Stefan worked for me.

Time(Time#('0538AM','hhmmTT'),'hh:mm') returned 05:38

Time(Time#('0538PM','hhmmTT'),'hh:mm') returned 17:38

[swuehl]

I also double checked.

I get 12:00:00 AM if I use

=time(num#(mid('0514am',1,4)))

or null using

=time(time#(mid('0514am',1,4)))

i.e. if I forget the format code for the string parsing.

=time(time#(mid('0514am',1,4),'hhmm'))

gives a correct time format depending on your system settings.

If you have still troubles, it it possible for you to upload a sample file?

Stefan

[#6]

how do i upload the file to the discussion?

[swuehl]

Hi Matt,

there is an upload function in advanced editor (link upper right corner of editor).

[#8]

Thanks i got it now. Im pretty new to this.

here is a sample file of the data.

Thanks so much.

Matthew

[swuehl]

I've added a listbox with expression to convert string TIME to a real time format.

You should be able to use that also in the script (without your data sources, I couldn't check).

Stefan

[#10]

would you mind posting the code?

I am not working on my copy of QV today but on an unregistered version so I cannot open the file.

That would be much appreciated.

Thanks