convert excel decimal total to minutes and seconds

steve h

New Member
Joined
May 6, 2008
Messages
4
Hello

I have listed a total amount of timings for a study that I have totalled giving me a figure of 34.76 which is under an hour.

how do i convert to min and seconds - is there a formula??

HELP!!!!!!!!!

:confused:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi & welcome,

Maybe something like:

=TIME(0,INT(A1),MOD(A1,1)*60)

Formatted as custom format [h]:mm:ss

Dom
 
Last edited:
Upvote 0
thanks

i am having some trouble with this because i have alist of times about 50 that add up to 26.87, i have entered them as general number e.g. 1.50 is 1 min 50 sec so they have all been totalled in decimals of 100, the cells are formatted as general numbers

i then need to convert the total to mins and secs
 
Upvote 0
34.76 represents the total of a lot of cells that have been entered into e.g. 1.32 is 1 min 32, 4.34 is 4 min 34 secs 5.21 is 5 mins 21 sec etc etc
 
Upvote 0
Maybe:

=TIME(0,INT(A1),MOD(A1,1)*100)

Dom
 
Last edited:
Upvote 0
sorry i am being very stupid here but i have in one coloumn

<TABLE style="WIDTH: 53pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=70 border=0><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><TBODY><TR style="HEIGHT: 15pt" height=20><TD id=td_post_1562048 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 53pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=70 height=20>1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>1.25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>total 5</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21></TD></TR>

<TR style="HEIGHT: 18.75pt" height=25><TD class=xl63 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" align=right height=25>00:05:00

</TD></TR></TBODY></TABLE>

the 1.25 is 1 min 25 secs so it is 4 lots of 1 min and 25 secs which is 5 min 40 sec but formula reads 5 ??????
 
Upvote 0
Try something like:

=SUM(TIME(0,INT(A1:A4),MOD(A1:A4,1)*100))

Confirmed with Ctrl+Shift+Enter as it's an array formula.
 
Upvote 0
Perhaps:

=SUBSTITUTE(A1,".",":")/60

or to sum the range:
=SUMPRODUCT(SUBSTITUTE(A1:A4,".",":")/60)

Format as m:ss
 
Upvote 0
Obviously it would be better to enter your times in a time format excel recognises, i.e. enter 3 minutes 42 seconds as 0:03:42 etc. then you can just sum the times.

If you have times in that format [i.e. in the format 3.42] in A1:A50 you could sum them with this formula

=SUMPRODUCT(TEXT(A1:A50*100,"0\:00\:00")+0)

format result cell as [h]:mm:ss
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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