Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm trying to format cells in Excel so that they display the total duration of phone calls as hh:mm:ss, but Excel is giving me errors.

Sometimes durations are only mm:ss (49:10), or even just ss (35), and I need them by default to change to 00:49:10 and 00:00:35 respectivly. However, when I select 'Custom' on the 'Number' tab when formatting the cells and enter either 00:00:00 or ##:##:##, Excel tells me -

Microsoft Office Excel cannot use the number format you typed.

Also, hh:mm:ss will not work for me, as I'm dealing in durations, not times. Is anyone able to tell me how do format this? Thanks.

share|improve this question
    
to clear it up - please show your input data and how you get the input - is it typed by hand? an import? calculated from other data? is the raw data a string or a time-value (for instance - NOW() would resolve to a number of 41208,5323841435 - at least for me right now)? –  Jook Oct 26 '12 at 10:45
    
Just seen your edit, and that has answered it for me. Thanks. –  David Gard Oct 26 '12 at 11:25

1 Answer 1

up vote 2 down vote accepted

You can use one of these:

[h]:mm:ss -> displays something like 27:59:59 or 28:00:00 (28 hours)

[m]:ss -> displays something like 180:59 or 181:00 (181 minutes)

[s] ->  displays something like 180 or 181 (181 seconds)

you can even extend those to

[h]:mm:ss,000

to get the milliseconds ;)

And just so you not wonder- a cell which has a value of 1 formatted with my last example would resolve to 24:00:00,000 - as a numeric 1 equals 24 hours in this format. But this is fine - any time-formulas and durations should work well with this.

Edit:

Well, there might be just another way, to be closer to what you stated so far:

00":"00":"00

But now a time like 24:10:05 would have to be entered as a number 241005.

I don't know if this is what you want.


Using the time-formats you would have to enter a complete time statement every time - otherwise excel does not know what you mean, when you type in 10 -> are these 10 seconds, minutes or hours? The same goes for 10:15 - is this 10 hours 15 minutes or 10 minutes 15 seconds?

It's just like that, when you can't answer it as a human, don't expect excel to do it ;)

share|improve this answer
    
Thanks for the reply, but this is not working. With a cell formatted as [h]:mm:ss, 35 minutes and 10 seconds (entered as 35:10) formats as 35:10:00, rather than 00:35:10. Also, 10 seconds (entered as 10) formats as 240:00:00 rather than 00:00:10. –  David Gard Oct 26 '12 at 10:37
1  
yes - because you would need to enter them as 00:35:10 or 00:00:10 - would you mind showing how this data is put into your cell? or you would need to use a number like 0,00011574 to get to 10secs –  Jook Oct 26 '12 at 10:39
1  
Your edit did the trick - 00":"00":"00. Enterint 10 shows 00:00:10, and 1434434 shows 143:44:34. This is how a report outputs the data (don't ask me why, the software we use is very unique, but not in a good way!), so that is fine for my purposes - they are total durations already, they do not need to be added by Excel. –  David Gard Oct 26 '12 at 11:29

Your Answer

 
discard

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.