Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a excel column with data like:


And would like to show it like:


What formula combination should i use to make this possible? This should(?) work, but isn't:



share|improve this question
up vote 6 down vote accepted

Well, you have different number of minutes in each case, so a single MID won't work. You might try:


The innermost SUBSTITUTE substitutes m by : (which is used in time) and the second removes s. You get the following after those two substitutes:


If you just convert those to time now, Excel interpretes there as hh:mm, which is not your intent. Put a "00:" in front to fool Excel:


Now it's in the hh:mm:ss format (somewhat), multiply by 1 to convert this into a value and format it as time: hh:mm:ss. This should do the trick :)

share|improve this answer
+1 for cheating Excel. But you have to convert the minutes to hours like the OP wants (thats not a big deal for you, but you forgot it^^) – nixda Aug 13 '13 at 18:37
@nixda Excel does it for you when you convert the value to the format hh:mm:ss =P. And thank you ^^ – Jerry Aug 13 '13 at 18:38
Ah missed the formatting thing. You're right – nixda Aug 13 '13 at 18:39
And it did! Thanks! – Khrys Aug 13 '13 at 18:43
@Khrys You're welcome :) – Jerry Aug 13 '13 at 19:31

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.