16 Replies Latest reply: Sep 30, 2015 3:54 PM by Adam Krepistman

# number to time converstion

I am uploading data from txt file and while file saved in txt file hrs, minutes and seconds get converted to a number.  how do I convert back to time in QV?

• ###### Re: number to time converstion

Could you give an example of the format of your text file and how the interpreted number looks like?

QV sometimes interpretes strings as date and time automatically, a time value is then stored as fraction of day, e.g 18:00:00 in the evening would translate to number 0.75

If you think that what has happened, you can use a format function to format your number given a format code, like

=Time( 0.75, 'hh:mm:ss')

or

=Time(0.75, 'hh:mm:ss tt')

Have a look at the HELP, search for 'format codes' and 'date and time functions'.

• ###### Re: number to time converstion

The top is original source file that I save as txt (below) and load to QV.  4:40 ACD time got converted to 280.060211182.   How do I convert back to 4:40 in QV?

• ###### Re: number to time converstion

If think that you have a time converted to minute+fraccion of minutes.  In this case

280.060211182/60=4.666667

0.66666667*60=40

• ###### Re: number to time converstion

Could be: =MakeTime(Floor(280.060211182/60),Floor(Frac(280.060211182/60)*60))

• ###### Re: number to time converstion

ok - that solve minutes / seconds but next one is hrs / minutes / seconds...  how would you approach that?

• ###### Re: number to time converstion

MakeTime(Floor(280.060211182/60),Floor(Frac(280.060211182/60)*60),Floor(Frac(Frac(280.060211182/60)*60)*60))

• ###### Re: number to time converstion

let me play with it to see if I can get it to work.

• ###### Re: number to time converstion

how are you reading in the file?  I could see a format changing in Excel but not from a txt file

any chnace you can send a subset of the file?

• ###### Re: number to time converstion

see attached

• ###### Re: number to time converstion

can you send a subset of the file you are reading in?  The attached already has the time converted

I would like to see if I can see where in the process the time is getting converted so I need a source of data that has the field in the time format

• ###### Re: number to time converstion

I dont have unconverted file.  the system allows me to save txt output format and thats when conversion takes place.  the unconverted format is just screenshot of the software.

• ###### Re: number to time converstion

My friend, you already have this file loaded in QV, just apply the formula that i give you before and get the results done.

• ###### Re: number to time converstion

I will try shortly and let  you know.

• ###### Re: number to time converstion

can you send a screen shot when you are loading the unconvered file - the screen from the file wizard that shows how the system is reading the data

If you skip the transform set in the wizard, the data will not get converted

I modified the file you sent me, put the time in and when I checked the Enable transformation step button, it was converted from time to a number but when I did not do that step and just clikced next, the time was not converted to a number

• ###### Re: number to time converstion

my source file (txt) has already converted.  thats what i load to QV.  in QV i have to make it back to original view - unconverted.

• ###### Re: number to time converstion

ahhh - I did not realize that because I can see QlikView converting it as well when certain steps are followed.

My understanding was incorrect - thought you were reading the dta from the initial source ,.txt file, did not realize you were getting a converted file