How to format Sum of hh:mm?

Posted on 2011-09-19
Medium Priority
Last Modified: 2012-05-12
Hello experts:

See attached example.  I have a data worksheet source showing hours & minutes but not formatted as such.  I want to sum two cells and format as hh:mm.  

Having trouble -- can you help?

Question by:garyrobbins
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36563203
Use this formula:


and format as a number with two decimal places.

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 1600 total points
ID: 36563210
Correction: use this formula:


and format as:


LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 36563217
My first post displays hours and fractions of hours.

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 100 total points
ID: 36563236
Right-click on the cell and select "Format Cells.." from the shortcut menu.
Select the Number tab, then select "Custom" from the list on the left.
In the Type box, enter the following

Click OK

You should see 4444:25

Expert Comment

ID: 36563242
I tried editing the cells to put :00 at the end, didn't work - so I put new values in column "P".

your values in column "I" are Mins and Secs, that's all

LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 300 total points
ID: 36563616
>your values in column "I" are Mins and Secs

Not really, slycoder. They are text values which means if you simply sum them like


then you get zero because SUM ignores text

You can verify further by using this formula in a blank cell


You get TRUE

If you use an addition operator, i.e.


then text values that look like real time or date values will be "co-erced" to those value - if excel sees a time value with a single semi-colon like 81:50 it will always interpret that value as hours and minutes (not minutes and seconds), so that last formula is a valid way to sum them - you don't need SUM function.

regards, barry

Author Closing Comment

ID: 36563978
Thank you all for the prompt responses and for adding explanations.

Hope you are ok with my point assignment.


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question