Convert number to time format

[markgraham123]

Hi,

Here is my sample data:

I want to convert number to hh:mm:ss format.

Please see attached app.

Any help is highly appreciated.

311 - 03:11:00

1258 - 12:58:00

84743 - 08:47:43

[Anil_Babu_Samineni]

What are the 311, 1258 ... I mean these are Minutes or Hours or something

[markgraham123]

Those are numbers.

I have 311, 1258...in my data

I want to show it as the req. O/P

311 - 03:11:00

1258 - 12:58:00

84743 - 08:47:43

[sunny_talwar]

What Time would these be and why?

1254? -> 12:54:00 or 01:25:40

123? -> 12:30:00 or 01:23:00

11231 - > 11:23:10 or 01:12:31

[markgraham123]

Here is the logic:

If it is 3 digit - 'hmm'      123 is 01:23:00 

  4 digit -     'hhmm'        1254 is 12:54:00

  5 digit - 'hhmms'             11231 is 11:23:01

6 digit - 'hhmmss'              103252 is 10:32:52

Thanks.

[markgraham123]

I just answered my question . lol

I'm using len(field) to define the format and it worked.

Thanks

[sunny_talwar]

84753 is a five digit number, but it can't be 84:75:30? but you mentioned it to be hhmms? Did you mean hmmss?

[sunny_talwar]

Not sure how you got it, but I used this

Table1:

LOAD Value,

Time(Time#(Num#(Repeat(0, Pick(Match(Len(Value), 3, 4, 5, 6), 1, 0, 1, 0)) & Value & Repeat(0, Pick(Match(Len(Value), 3, 4, 5, 6), 2, 2, 0, 0))), 'hhmmss'), 'hh:mm:ss') as Time;

LOAD * INLINE [

    Value

    123

    1254

    11231

    103252

    311

    1258

    84743

];

[markgraham123]

If(len(Value)=6,

Time(Time#(Value,'hhmmss'),'hh:mm:ss'),

If(len(Value)=5,

Time(Time#(Value,'hmmss'),'hh:mm:ss'),

If(len(Value)=4,

Time(Time#(Value,'hmms'),'hh:mm:ss'),

If(len(PLTLM)=3,

Time(Time#(Value,'hmm'),'hh:mm:ss'))))) as Value_Final;

Sorry sunny. 5-digit is 'hmmss'

[markgraham123]

sorry sunny ..

5 digit is hmmss