Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have field with time values mentioned as
24 min 37 sec,
25 min 31 sec,etc
I want to convert this in time format as HH:MM:SS .
Try this:
Load
[Resolution Time],
Time(
Alt(Time#(TimeFormatted,'hhAAmmBBssCC'),
Time#(TimeFormatted,'mmBBssCC'),
Time#(TimeFormatted,'hhAA'),
Time#(TimeFormatted,'mmBB'),
Time#(TimeFormatted,'ssCC'))
, 'hh:mm:ss') as ConvertedTime;
Load
Replace(Replace(Replace(Replace([Resolution Time], 'hour','AA'),
'min', 'BB'), 'sec', 'CC'), ' ','') as TimeFormatted,
[Resolution Time]
From <>;
Do you also have value like:
2 hours 10 mins 30 secs
or, it would be like
130 mins 30 secs ?
Yes. I have values like
2 hours 10 mins 30 sec
Could you share a set of sample data (at least 10 values) with various formats possibilities?
Resolution Time |
18 min 52 sec |
21 min 20 sec |
23 min 19 sec |
21 min 11 sec |
22 min 31 sec |
19 min 49 sec |
21 min 41 sec |
24 min 37 sec |
25 min 31 sec |
14 min 31 sec |
22 min 21 sec |
18 min 4 sec |
25 min |
25 min 19 sec |
19 min 30 sec |
21 min 33 sec |
21 min 57 sec |
18 min |
20 min 22 sec |
31 min |
25 min 8 sec |
21 min 8 sec |
20 min 18 sec |
26 min 36 sec |
17 min 19 sec |
24 min 28 sec |
15 min |
17 min 27 sec |
23 min 16 sec |
20 min 8 sec |
19 min 9 sec |
20 min 25 sec |
34 min 33 sec |
23 min 30 sec |
33 min 43 sec |
24 min 11 sec |
23 min 5 sec |
21 min 59 sec |
19 min 46 sec |
25 min 39 sec |
23 min 19 sec |
25 min 5 sec |
28 min 53 sec |
20 min 33 sec |
22 min 19 sec |
26 min 29 sec |
20 min 33 sec |
22 min 2 sec |
22 min 27 sec |
27 min 32 sec |
36 min 28 sec |
31 min |
28 min 56 sec |
22 min 38 sec |
18 min 26 sec |
13 min 26 sec |
20 min 4 sec |
34 min 59 sec |
28 min 53 sec |
28 min 3 sec |
20 min 32 sec |
33 min 6 sec |
25 min 54 sec |
20 min 17 sec |
15 min 50 sec |
20 min 32 sec |
19 min 12 sec |
20 min 44 sec |
16 min 39 sec |
29 min 17 sec |
35 min 23 sec |
24 min 12 sec |
18 min 52 sec |
16 min 30 sec |
27 min 6 sec |
27 min 33 sec |
17 min 32 sec |
13 min 13 sec |
12 min 40 sec |
14 min 43 sec |
39 min 2 sec |
18 min 23 sec |
1 min |
Try this:
Load
[Resolution Time],
Time(
Alt(Time#(TimeFormatted,'hhAAmmBBssCC'),
Time#(TimeFormatted,'mmBBssCC'),
Time#(TimeFormatted,'hhAA'),
Time#(TimeFormatted,'mmBB'),
Time#(TimeFormatted,'ssCC'))
, 'hh:mm:ss') as ConvertedTime;
Load
Replace(Replace(Replace(Replace([Resolution Time], 'hour','AA'),
'min', 'BB'), 'sec', 'CC'), ' ','') as TimeFormatted,
[Resolution Time]
From <>;
Thank You ! It's working