Converting added minutes to show as an hour

[SelinaR]

Morning all....

Tried various ways to convert the following into showing me hours... please help (I dont want it in a clock format - just a formula answer)

Minutes taken
0.12
0.35
0.50
=SUM(a1:a3) : Answer 0.97 (but I want it to look like 1.37 --> 1 hour and 37 minutes)

please help????

THANKS IN ADVANCE!!!

 

[rjwebgraphix]

Is this what you want?

Excel Workbook
	A	B
1	0.12	What
2	0.35	to
3	0.5	sum
4	0.97	Total
5	0:58:12	Timeformat
Sheet

 

[JoeMo]

Ignore this post and see next post.

=int(sum(a1:a3)*100/60)&"."&mod(sum(a1:a3)*100,60)

 

[JoeMo]

=int(sum(a1:a3)*100/60)&"."&if(mod(sum(a1:a3)*100,60)<10,"0"&mod(sum(a1:a3)*100,60),mod(sum(a1:a3)*100,60))

 

[Scott Huish]

If you are using a version before XL2007, you will need to load the Analysis Toolpak for this:

=DOLLARDE(SUM(A1:A3),60)/24

Format as [h]:mm

 

[northwolves]

=TEXT(TIME(0,sum(A1:A3)*100,0),"h.mm")

 

[Scott Huish]

I tried something similar to that before my solution. Where it doesn't quite work out, is if there was an entry like 1.50 (instead of the .5 in the sample data), meaning 1 hour and 50 minutes.

My formula gives 2:37, while yours gives 3.17

Excel Workbook
	A	B	C
1	0.12	2:37	3.17
2	0.35
3	1.5
Sheet1

 

[northwolves]

I tried something similar to that before my solution. Where it doesn't quite work out, is if there was an entry like 1.50 (instead of the .5 in the sample data), meaning 1 hour and 50 minutes.

My formula gives 2:37, while yours gives 3.17

Excel Workbook
	A	B	C
1	0.12	2:37	3.17
2	0.35	*	*
3	1.5	*	*
Sheet1

You are right,thanks.

=TEXT(TIME(SUMPRODUCT(INT(A1:A3)),100*SUMPRODUCT(MOD(A1:A3,1)),0),"h:mm")