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
garyrobbinsAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Correction: use this formula:

=SUM(I18+I16)

and format as:

[HH]:MM

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Use this formula:

=SUM(I18+I16)*24

and format as a number with two decimal places.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
My first post displays hours and fractions of hours.

Kevin
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Glenn RayConnect With a Mentor 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
 
slycoderCommented:
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
 
barry houdiniConnect With a Mentor 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
 
garyrobbinsAuthor 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.

All Courses

From novice to tech pro — start learning today.