help converting values (i.e. 54, 123, 1134) to time (i.e. 00:54, 01:23, 11:34)

[kevinweinberg]

The subject best described it and i'm trying to use this formula but it doesn't work:

B8 is the time value un-formatted

=if(B8=len(2),"00:"&B8,if(B8=len(3),"0"&text(B8,0:00),text(B8,00:00)))

I can't figure out if it doesn't like my ""'s or no... help??

Thanks!!

[Pepe Le Mokko]

What is 00:54 ? Hours and min or min and sec?

[Pepe Le Mokko]

Does this help

Please Login or Register  to view this content.

?
Format as hh:mm:ss

[kevinweinberg]

That worked perfectly!! Thank you! how does that work??

[Pepe Le Mokko]

Using the Evaluate formula tool you can see how the new string is created.Adding 0 ( or multiplying by 1) coerces the obtained string to it's numerical value

[Tony Valko]

Maybe this...

=--TEXT(A2,"00\:00")

Format as h:mm

[Pepe Le Mokko]

Maybe this...

=--TEXT(A2,"00\:00")

Format as h:mm

Much better !

[Tony Valko]

Thanks!