Totaling hours in hh:mm format

Dear all

On the attached time list report I have converted Seconds (Column G) to Time as hh:mm format (Column H) and I m trying to get total for each day (Column C) on Total Hours (Column I), but some reason date 08/12/2015 total doesn't add up on Colum I, the rest is ok.

What I noticed if I delete 3 rows on day group 8/12/2015 then it changes the total to correct amount on Colum I.

Can you please help me why the formula on Colum I doesn't give me correct total if it is more than 12 rows?

Thank you for your help in advance

You need to apply a custom format in that column of:

[h]:mm

where the square brackets prevent the time displays from wrapping at 24 hours - if you do that to your sample then you will see 25:30 in cell I24.

Hope this helps.

Pete

thank you very much guys all 3 answers were corrected the problem. You all are stars
kind regards

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

One more problem, why if the hours minus and plus doesn't give correct total, for example -06:00 + 06:00 = should be 00:00 but it doesn't total correctly.
do you know why?

here is the excel sheet. On 02/12/2015 at 5. row has a minus entry but it doesn't taken off the group total by formula.

Thank you Glenn , I don't think I will do the switch as it will change all my aged debt differences. Maybe what I need is manually delete minus entries with matching entries

kind regards

You can use this formula in I4:

=IF(COUNTIF(C:C,C4)=COUNTIF(C$1:C4,C4),SUMIF(C:C,C4,G:G)/86400,"")

then you don't need to delete -ve entries and matching +ve ones.

Hope this helps.

Pete

This has worked, thank you very much once again Pete_UK

Originally Posted by Pete_UK

You can use this formula in I4:

=IF(COUNTIF(C:C,C4)=COUNTIF(C$1:C4,C4),SUMIF(C:C,C4,G:G)/86400,"")

then you don't need to delete -ve entries and matching +ve ones.

Hope this helps.

Pete

That's the total that you get if you change to my formula in Post #13, as it is acting directly on the seconds in column G.

Pete

EDIT: Ah, we crossed in the post.

Pete

thank you very much again

You're welcome.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete