Inserting duration in a column without using colon and adding them up

[masud8956]

I have a column with 56 rows. I want to insert duration in [h]:mm format without having to type colon. For that I formatted input cells using 0\:00 to get a return at row 57. The problem is, while adding up those duration I am getting erroneous results.

e.g. 0:30 and 0:40 are added up as 0:70 but I expected 1:10.

What did I do wrong? Is there any solution without using VBA?

Thanks in advance.

 

[Kaper]

check

=(mod(A1,100)/60+int(A1/100))/24+(mod(A2,100)/60+int(A2/100))/24

and format cell with formula as [h]:mm


or

=text(mod(A1,100)/60+int(A1/100))/24+(mod(A2,100)/60+int(A2/100))/24,"[h]:mm")

to sum from whole column you would need use similar approach in Array (committed Ctrl+Shift+Enter) formula like:

=sum((mod(A1:A64,100)/60+int(A1:A64/100))/24)

let me emphasize again it's ARRAY formula.

 

[masud8956]

Thank you very much! It worked nicely!

 

[masud8956]

Sorry to bother you again!

The MOD function worked perfectly for straight forward calculations! But in one occasion in the same sheet I am in need of using {=SUMIF(TYPE,H146,D_DUAL)} function where I have filtered data basing on a drop down list (TYPE, D_DUAL). What I have done is I have used {=SUM((MOD(I146,100)/60+INT(I146/100))/24)} function in another row just to convert the result in the form of {h}:mm. Problem is the results are erroneous. e.g. 0:45+0:45+0:45 adds up to 1:35 where as it should had been 2:15. Here to mention that all my input data are in 0\:00 format. I want the output in [h]:mm format.

How can I fix it?

Thanks in advance!

 

[Kaper]

Try summing hours and minutes separately.
something like:
{=SUM(MOD(I1:I200,100)/60/24)+SUM(INT(I1:I200/100)/24)}