How can I display a cumulative total of hrs as hhh:mm

[Chinny]

How can I display a cumulative total of hrs as hhh:mm, after addin
cells derived from the following type of formula

=TEXT(CONVERT(SUM(A1:A#),"mn","hr")/24,"h:mm")

A1-A#= Occurance of a Task per day
B1-B# = Time to perform task once
C1-C# = Time to perform Task per day
D1-D# = Time to perform Task per week
E = segragates the types of tasks into categories. eg - manual
automatic etc...(made of merged cells)
F = Total time per day, per category (made of merged cells)
G = Total Time Per Month Per Category (results in hh:mm or hhh:mm)

at the bottom of each column there is a cell with the total time fo
that column.

So

C = A*B
D = C*5 - 5 day working week. - derived using the above formula
E = how ever many Rows per category.
F = Total of merged cells in each section in E - derived using th
above formula
G = F*20 - 20 working days per month.

I can't seem to get G to give me the totals expected - hhh:mm

Any ideas?

:confused

 

[Frank Kabel]

Hi
format the target cell with the custom format
[hh]:mm

 

[Chinny]

Found the answer myself in the end, although thanks Frankie.

Two problems I had

Instead of using the below formula and and trying to add merged cell
(which seemed for some reason to complicate matters a little)

=TEXT(CONVERT(SUM(A1:A#),"mn","hr")/24,"h:mm")

I should have used the following formula

=sum(TEXT(CONVERT(SUM(A1:A#),"mn","hr")/24,"h:mm")*20)

=G detailed in original post

to give me the total hrs to work with. eg 0:26, but this will stil
only give me 0.36111111 until you do the following

->right click
->format cells
->numbers
->custom
-> select [h]:mm
NB this doesn't have to be [hhh]:mm

this will then return the time in the correct format. All cells don'
have to have the same format though for adding together.

As long as the final cell where you wish to have to total placed ha
this format, you will acquire the correct display

so adding (hh:mm)
88:00
8:40
13:00
8:40
+ 3:00