Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
manoranjan_d
Specialist
Specialist

converting in to time format

I am pulling some data from Excel, a column has value in min and second (e.g. 115.533333333333 ). this is 115 minutes and rest are milliseconds


I want to convert this value in HH:MM:SS


Interval(round(elapsed_time_in_minutes*60),'ss') as elapsed_time_in_min_1,

            Interval(round(elapsed_time_in_minutes*60),'mm.ss') as elapsed_time_in_min_2,

            Interval(round(elapsed_time_in_minutes),'mm.ss') as elapsed_time_in_min_3,

            interval(timestamp#(elapsed_time_in_minutes,'s'),'hh:mm:ss') as elapsed_time_in_min_4,

            Interval(Interval#(elapsed_time_in_minutes/1000,'ss'),'mm:ss')as elapsed_time_in_min_6,

            TimeStamp((elapsed_time_in_minutes/(1000*60*60*24)),'mm:ss')as elapsed_time_in_min_5;


but non are working


pls help me

8 Replies
Anil_Babu_Samineni

Do you know the value of given number in hh:mm:ss format??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

Just this?

=Interval(Interval#('115.533333333333','mm.fff'), 'HH:mm:ss')

Capture.JPG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
manoranjan_d
Specialist
Specialist
Author

hi anil,

basic requirement is 115.53333

115 minutes and rest are milliseconds


now in the script level i want to convert this value in HH:MM:SS

manoranjan_d
Specialist
Specialist
Author

hi anil,

pls dont pass value its a field i want to covert this into the script level not int he chart or test box level

avkeep01
Partner - Specialist
Partner - Specialist

Hi Manoranjan,

You could use:

INTERVAL(elapsed_time_in_minutes/60/24,'hh:mm:ss')

Or

// only inminutes

INTERVAL(FLOOR(elapsed_time_in_minutes)/60/24,'hh:mm:ss')

If the decimal numbers are in miliseconds (not seconds) you could use:

=INTERVAL((FLOOR(elapsed_time_in_minutes)/60/24)+(elapsed_time_in_minutes - FLOOR(elapsed_time_in_minutes))/100/60/24,'hh:mm:ss')

Results:

01:55:32 (1 hour, 55 minutes and 32 seconds)

Or

01:55:00

Anil_Babu_Samineni

Try this?

=Interval(Interval#(FieldName,'mm.fff'), 'HH:mm:ss')


Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Chanty4u
MVP
MVP

may be try with this  its working for me

tim.PNG

Or
MVP
MVP

I think you're presenting the value wrong - there's no way the value after the period is milliseconds (533333333333). If it was, it would represent roughly 17 years.

If it represents part of a minute, you have to figure out if it represents a fraction (in which case the value is roughly 01:55:31) or a representation of seconds (in which case the value is 01:55:53). Neither one is particularly complicated to format, but you have to know which one you're looking for.