• 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

    Hui...

  • 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

BroadwayDubbing

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?

Thanks!
Luciano P
 

Attachments

Chihiro

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:
=TIMEVALUE(A2)

Copy down.

Then format as whatever time format you want.
 
Top