# How to format Sum of hh:mm?

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?

Gary
Kronos-Employee-Weekly-Hrs-11082.xls
###### Who is Participating?

ConsultantCommented:
Correction: use this formula:

=SUM(I18+I16)

and format as:

[HH]:MM

Kevin
0

ConsultantCommented:
Use this formula:

=SUM(I18+I16)*24

and format as a number with two decimal places.

Kevin
0

ConsultantCommented:
My first post displays hours and fractions of hours.

Kevin
0

Excel VBA DeveloperCommented:
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
[h]:mm

Click OK

You should see 4444:25
0

Commented:
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

Corrected---Kronos-Employee-Week.xls
0

Commented:
>your values in column "I" are Mins and Secs

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

=SUM(I18,I16)

then you get zero because SUM ignores text

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

=ISTEXT(I16)

You get TRUE

If you use an addition operator, i.e.

=I16+I18

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
0

Author Commented:
Thank you all for the prompt responses and for adding explanations.

Hope you are ok with my point assignment.

Gary
0
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.