# Converting added minutes to show as an hour

#### SelinaR

##### Board Regular
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)

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Is this what you want?
Excel Workbook
AB
10.12What
20.35to
30.5sum
40.97Total
50:58:12Timeformat
Sheet

Ignore this post and see next post.
Code:
``=int(sum(a1:a3)*100/60)&"."&mod(sum(a1:a3)*100,60)``

Last edited:
Code:
``=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))``

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

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

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
ABC
10.122:373.17
20.35
31.5
Sheet1

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
ABC
10.122:373.17
20.35**
31.5**
Sheet1

You are right,thanks.
Code:
``=TEXT(TIME(SUMPRODUCT(INT(A1:A3)),100*SUMPRODUCT(MOD(A1:A3,1)),0),"h:mm")``

Replies
3
Views
109
Replies
3
Views
298
Replies
15
Views
234
Replies
3
Views
426
Replies
3
Views
223

1,196,414
Messages
6,015,126
Members
441,873
Latest member
saustark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back