Convert alphanumeric uration into HH:MM:SS

[Anonymous]

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

[adamdavi3s · Accepted solution]

Hi Vals,

Not that easy to do, can you provide a sample dataset?

I am sure it can be done though

[adamdavi3s]

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.

[sdmech81]

PLss send sample data..Hope we need to use string functions to arrive at that

Sachin

[Anonymous]

[Anonymous]

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

[adamdavi3s]

Hi Vals,

Not a problem I can write this for you.

What happens when the time is < 1hour?

[Anonymous]

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 .

[adamdavi3s]

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

];

[MarcoWedel]

Hi,

maybe helpful:

Convert string to the timestamp

regards

Marco