Format for Elapssed Time not Time of Day (And I have tried mm:ss)

[Im_Offset]

Hello everyone,

I am trying to format a column to show elapsed time for videos I have. So if a video is 6 minutes and 48 seconds, I would like the result to shoe 6:48 (If the video is 1 hour 12 minutes and 13 seconds, I would like the result to be 72:13). I have tried the custom format mm:ss, but the result for 6:48 is 48:00 and the result for 73:12 is 31:12, and the result for 72:13 is 13:00. I made the custom format mm:ss.00 and the result for 6:48 is 48:00.00 and the result for 72:13 is 13:00 sometimes and 31:13:00 others! What am I doing wrong?

 

[Chihiro]

Format as [m]:ss.00 or [m]:ss

 

[Im_Offset]

ok. Cool. What is it about asking for things with the "m" in [ ] that does the trick?
Also, is there a way that I can type in a number like 6.48 but have it show up as 6:48? Typing in the ":" is a pain.

 

[Chihiro]

Basically by encasing minutes in [], it removes the ceiling of 60 min in each hour. Otherwise, it will move anything over 60min into another hour. With mm:ss format, it's just excluding the hour portion from display.

2nd portion. Can you upload sample of how your data comes in and where the expected output should go?

 

[Im_Offset]

The first column is just a video number (I'll name it later). The second column is the length of the video (I had to format it as [h]:mm to get the correct look. You would think [m]:ss would give the same format result, but it doesn't!). The first 40 videos are listed with the correct time. I had to input the colon manually. But it is a pain to type the colon each time because you have to take your hand away from the number pad, press the ,<shift> button and then the colon. Is there a way for me to type in 6.48 and have the format show 6:48?

 

[Chihiro]

If you wanted to use [m]:ss format, you need to type 0:mm:ss into the cell, if you typed just mm:ss in cell it will consider mm portion as hour.

For your 2nd portion, you need either helper column or VBA.

See E2:F2 for how to do it using Helper Column.
F2 formula:
=TIME(0,LEFT(E2,FIND(".",E2)-1),RIGHT(E2,LEN(E2)-FIND(".",E2)))

F2 is formatted as [m]:ss

 

[Im_Offset]

Thank you...I learned something today!