• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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
0
garyrobbins
Asked:
garyrobbins
3 Solutions
 
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:
Correction: use this formula:

=SUM(I18+I16)

and format as:

[HH]:MM

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

Kevin
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Glenn RayExcel 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 houdiniCommented:
>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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now