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: 
Anonymous
Not applicable

Convert time format

Hello

in my excel file, I have data in the format hh: mm: ss that I want to transform in the format hh, m in the script when loading data.

today I can not transform my data into the desired format.

Thanks for your help

1 Solution

Accepted Solutions
sunny_talwar

Maybe try this

Num(Time)*24

Capture.PNG

View solution in original post

13 Replies
sunny_talwar

May be this

Time(Time#(FieldName, 'hh:mm:ss'), 'hh, m') as FieldName

Anonymous
Not applicable
Author

Thank you but it does work, I have nothing in my new field...

dwforest
Specialist II
Specialist II

You may need to manually parse the data if it is formatted as General (string) rather than a date/time in Excel

Time(MakeTime(num(left(FieldName,2)), num(mid(FieldName,3,3))),'hh, m')

Anil_Babu_Samineni

today I can not transform my data into the desired format.

Not sure, I followed this part.

Perhaps this?

If FieldName = Today() Then

Load FieldName From Table;

Else

Load Time(Time#(FieldName, 'hh:mm:ss'), 'hh, m') as FieldName From Table;

End If;

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)
sunny_talwar

Would you be able to share few rows of data to see your issue?

Anonymous
Not applicable
Author

Here are some lines of my Excel file.

I have also some hours in the colum "Time" which are higher than 24:00:00 (max: 239:00:00)

I created the second column to be able to calculate the total.

Time            Time in hour

0:02:530,05
0:16:480,28
0:10:000,17
0:14:510,25
0:10:000,17
0:10:000,17
1:19:171,32
0:00:000,00
0:00:000,00

Thank you

Anonymous
Not applicable
Author

No I doesn't work, I have "-" in my field

sunny_talwar

Maybe try this

Num(Time)*24

Capture.PNG

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you parse this using the Interval#() function it should be correct, even when hh>24.

Interval(Interval#(Time, 'h:mm:ss'))

Can you post the script you are trying?

-Rob