10 Replies Latest reply: Dec 7, 2017 8:19 PM by Anil Samineni

# Convert number to time format

Hi,

Here is my sample data:

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

Any help is highly appreciated.

311 - 03:11:00

1258 - 12:58:00

84743 - 08:47:43

• ###### Re: Convert number to time format

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

• ###### Re: Convert number to time format

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

• ###### Re: Convert number to time format

It's already been Assume Answered. Not sure, Whether you got to work or not. Perhaps this?

Alt(Date(Date#(Value, 'hmm'),'hh:mm:ss'),Date(Date#(Value, 'hhmm'),'hh:mm:ss'),Date(Date#(Value, 'hhmmss'),'hh:mm:ss'))

OR

LOAD Value, Len, Pick(Match(Len, 3,4,5), Date(Date#(Value,'hmm'), 'hh:mm:ss'),Date(Date#(Value,'hhmm'), 'hh:mm:ss'),Date(Date#(Value,'hhmmss'), 'hh:mm:ss')) as Time;

LOAD *, Len(Value) as Len Inline

[

Value

311

1258

84743

];

• ###### Re: Convert number to time format

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

• ###### Re: Convert number to time format

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.

• ###### Re: Convert number to time format

I just answered my question . lol

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

Thanks

• ###### Re: Convert number to time format

Not sure how you got it, but I used this

Table1:

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;

Value

123

1254

11231

103252

311

1258

84743

];

• ###### Re: Convert number to time format

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'

• ###### Re: Convert number to time format

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?

• ###### Re: Convert number to time format

sorry sunny ..

5 digit is hmmss