0

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.

Improve this question
2
  • 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
    Commented Oct 26, 2012 at 10:45
  • Just seen your edit, and that has answered it for me. Thanks.
    – David Gard
    Commented Oct 26, 2012 at 11:25

1 Answer 1

Reset to default
2

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 ;)

Improve this answer
3
  • 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
    Commented Oct 26, 2012 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
    Commented Oct 26, 2012 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
    Commented Oct 26, 2012 at 11:29

You must log in to answer this question.

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