Converting added minutes to show as an hour

SelinaR

Board Regular
Joined
Feb 2, 2012
Messages
62
Office Version
  1. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
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!!!
 

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.
Ignore this post and see next post.
Code:
=int(sum(a1:a3)*100/60)&"."&mod(sum(a1:a3)*100,60)
 
Last edited:
Upvote 0
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))
 
Upvote 0
ADVERTISEMENT
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
 
Upvote 0
Code:
=TEXT(TIME(0,sum(A1:A3)*100,0),"h.mm")
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top