Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .