Multiplying mm:ss by a number

[MattMax]

I'm looking to multiply mm:ss by a number and get mmm:ss
Instead, excel looks to be converting the time into a date.

There has to be a simply solution to this... I going nuts trying to find it.

To start, if I enter a 5:40 into a cell Excel automatically formats as "5:40:00 AM" (I want it in mm:ss) I don't seem to have any luck with changing the format of the cell as time with 13:30 option if I use the custom mm:ss it turns the time into 40:00 for some reason.

Now if I multiply 5:40:00 AM by 5 I get "4:20" (excel adds a day 1/1/00)

Is there a simple way to multiply mm:ss against a number without adding days?

Many thanks in advance.

 

[pgc01]

Hi

If you enter 5:40 you are not entering 5 minutes and 40 seconds, you are entering 5 hours and 40 minutes

To enter 5 minutes and 40 seconds, try:

0:5:40

 

[ljungren13]

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>00:05:40</TD><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64>02:21:40</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">141:40</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 344pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=458 border=0><COLGROUP><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 2560" width=140><COL style="WIDTH: 239pt; mso-width-source: userset; mso-width-alt: 5814" width=318><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 105pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=140 height=20>00:05:40</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 239pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=318>=A1*25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20> </TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">=((HOUR(A1*25)*60)+MINUTE(A1*25))&":"&SECOND(A1*25)</TD></TR></TBODY></TABLE>

Is this what you are asking? Of course formatting makes a big difference... when you format it in mm:ss you still have to enter your time in as 00:05:40

 

[MattMax]

Thanks I just made a break through.. I'm using a custom format "[h]:mm" which accumulates the hours and doesn't add a day.

I'm okay with shoehorning the data in as hours.. because it's easier to enter that way...

Thanks!
MattMax