Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I have a excel having the time duration in alphanumeric (e.g. 1h30m , 2h) . I want to convert duration into HH:MM format .
Please help me on the same .
Thanks,
Vals
Hi Vals,
Not that easy to do, can you provide a sample dataset?
I am sure it can be done though
Hi Vals,
Not that easy to do, can you provide a sample dataset?
I am sure it can be done though
Tell a lie, I found an example of where I have done this before:
Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.
This will help users identify the answers should they come across this thread in the future.
PLss send sample data..Hope we need to use string functions to arrive at that
Sachin
This is the format of the xlsx and in qlikview workbook i want it in HH:MM format . I tried to use SUBFIELD and TRIM function to make it happen but unfortunately no result .
Thanks
Hi Vals,
Not a problem I can write this for you.
What happens when the time is < 1hour?
Hi Adam,
Actually its the master file given by user . So manually inserted data and time is not less than 1 hr each and every events are having duration more than 1 hr .
Here is my sample script which loads:
LOAD rowno() as id,*,
subfield(time,'h',1)&':'&if(len(subfield(time,'h',2))<1,00,subfield(time,'h',2)) as timestamp
;
LOAD * INLINE [
time
1h
2h30
1h
1h30
1h
1h
3h30
1h
1h
1h
];