• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying [hh]:mm:ss Format


New Member
Hi forum,

I attached a file for reference. In column A (total runtime 1) I have values in cells custom formatted as [hh]:mm:ss. I cannot produce the sum of the total runtime.
In column B (total runtime 2) I have the same values and the same custom formatting, but here I selected each value in each cell on the Formula Bar and pressed Enter. This way I get a format like 12:05:58 AM instead of 00:05:58 and I can sum the total runtime (00:32:13). Of course selecting each single cell, clicking the value in the Formula Bar and pressing Enter for several cells it's time consuming. What am I doing wrong?

Luciano P



Excel Ninja
Issue isn't formatting, but that your time is stored as text and not as time value.

Select range, Text to Columns. Hit next until you reach Step 3 of 3. There set column data format to Date (MDY). Finish.

Alternately you can use formula and helper column.
In B2:

Copy down.

Then format as whatever time format you want.