convert number to time

[Guest]

I would like to ask for help.

I have to convert a number 5.27 to time 5:16 as minutes and seconds. I am
using the following to convert: =perform_dump!B35/24/60, however the return
is showing an AM/PM and I just need minutes and seconds to I can run
variances.

Any solutions?

Thanks

 

[Peo Sjoblom]

Just format as mm:ss

or if you don't mind if it is text


=TEXT(perform_dump!B35/24/60,"mm"ss")

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

Format the cell using the format string

mm:ss

Do this by:
Format>Cells>Number & select Custom. Enter the string above in the Type box

 

[Guest]

Peo,

I am receiving a "#VALUE!" I have added an extra " where it was missing.
Any thoughts

 

[Guest]

I think I got it, it should look like this right:

=TEXT(perform_dump!B35/24/60,"mm:ss")

 

[Peo Sjoblom]

Correct

Peo

I think I got it, it should look like this right:

=TEXT(perform_dump!B35/24/60,"mm:ss")

 

[Guest]

One more question

how do I use the same solutions when converting to text if the existing
formula is this: =VLOOKUP(BV1,Schedule_Dump!C130:I155,4,FALSE).

I tried using this:
=TEXT(VLOOKUP(BV1,Schedule_Dump!C130:I155,4,FALSE),"hhh:mm") but it is
returning 1:00, when the return should be 433:00.

Thanks for your help.

 

[Dave Peterson]

try a format of:
"[hh]:mm"

instead of:
"hhh:mm"

One more question

how do I use the same solutions when converting to text if the existing
formula is this: =VLOOKUP(BV1,Schedule_Dump!C130:I155,4,FALSE).

I tried using this:
=TEXT(VLOOKUP(BV1,Schedule_Dump!C130:I155,4,FALSE),"hhh:mm") but it is
returning 1:00, when the return should be 433:00.

Thanks for your help.