Need to extract mins and secs from the text field

[Anonymous]

I need to extract mins and secs from a field that is in text format

My field name is [Resolution time] and the values are pasted below . I have used the below to extract hours trim(left([BPS Response Duration],index([BPS Response Duration],' hours')))  as Hrs, but finding it difficult mins and secs. Can anybody please help how do i go about extract mins and secs..

0 hours

3 mins 48 secs

4 secs

6 secs

8 secs

9 secs

10 hours 30 mins

10 secs

11 secs

13 secs

14 secs

15 secs

18 secs

21 secs

25 secs

28 hours 45 secs

29 hours 23 mins 45 secs

34 mins 43 secs

43 hours 56 mins 10 secs

50 hours 54 mins

120 hours

1000 hours

2000 hours

48 mins

[sunny_talwar]

Set your time format like this:

SET TimeFormat='h:mm:ss';

or change the script like this:

Interval(Alt(Num(Interval#(NewField, 'h a mm b ss c')), Num(Interval#(NewField, 'mm b ss c')), Num(Interval#(NewField, 'h a')), Num(Interval#(NewField, 'mm b')), Num(Interval#(NewField, 'ss c')), Num(Interval#(NewField, 'h a ss c')), Num(Interval#(NewField, 'h a mm b'))), 'h:mm:ss')  as TimeStamp

[Anonymous]

it worked finally..when i replaced interval# with timestamp# it worked but when i replaced interval with timestamp it didnt? will you be able to explain why you used interval?

[sunny_talwar · Accepted solution]

TimeStamp is just it looks, it is a timestamp, whereas interval is used for duration. Your machine started at 12/08/2016 5 am and kept running for 52 hours and 20 mins. First is a timestamp and second is a duration (interval).

Does that make sense

[sunny_talwar]

Hi Sanjyot -

I am glad that you follow the 'unenforced' rule of marking correct and helpful response, but I would suggest you to reconsider what you have marked as correct and/or helpful. The response that actually helped you reach your final goal has neither been selected as correct or helpful. Lets just consider that you are looking for something and land up on a page like this what would be the first think you check? Correct response or helpful response? Guess what, in this thread you will have to make more effort than that?

What I am trying to suggest that mark correct and helpful responses not because you have an obligation to your responders and you want to give them additional points. Mark them because you have an obligation towards the community. Mark them because these responses will help others when they land on this page for answers.

Thanks for your time reading above and I hope you will reconsider your choices.

Thanks,

Sunny