Format a number to time

[jcharles]

Good day

Please can you help me format a number to a time?

Numbers

600

612

700

730

1805

The times for the above should be:

06:00

06:12

07:00

07:30

18:05

I've tried using Time#(Number,'HH:MM') and Time(Number,'HH:MM') but both do not seem to work.

Please help - Thank you

[CELAMBARASAN]

Hi,

It should be

Time(Time#(Number,'Hmm'), 'HH:mm')

Hope it helps

[tresesco · Accepted solution]

Try like:

If(Len(Numbers)=3,Time(Time#(Numbers,'hmm') ,'hh:mm'), Time(Time#(Numbers,'hhmm') ,'hh:mm') ) as NewTime

[ashfaq_haseeb]

Hi try below

=time(time#(Numbers,'hmm'),'h:mm')

Regards

ASHFAQ

[jcharles]

Great!!! - Thank you Thank you

[tresesco]

Does it work for 4-digit number ?

[#7]

Try this:

LOAD *,

Time#(Mid(Number,1,2)  & ':' & Mid(Number,3,2),'HH:mm') as FormatedTime;

LOAD if(len(Number)=3,'0'&Number,Number) as Number;

LOAD * INLINE [

    Number

    600

    612

    700

    730

    1805

];

[jcharles]

Wonderful - this takes into account the change in the length of the number too - Thank you

[CELAMBARASAN]

No I missed that. But you can implement like below, instead of going for If condition

Time(Time#(Num(Number, '0000'), 'HHmm'), 'HH:mm')

[anbu1984]

Or like this

Time(Time#(Left('0'&Number,4),'HHmm'), 'HH:mm')