I have a cell stating a tape lesson duration of time as being 2:44.

The next cell will be the number of times that tape lesson has been completed. e.g. 10.

How do I write the correct formula to calculation the time 2:44 * 10 and have it come out accurately as the total time for that lesson?

Hello Make this little macro in excel (Tools -> Macro -> Visual basic editor). Insert a new module file, and paste this function there.

-------------------------------------------------

Function Multiply(Tape As String, NoOfTimes As Integer) As String

Dim residue As IntegertotalMinutes = Right(Tape, Len(Tape) - InStr(1, Tape, ":")) * NoOfTimes

totalhours = Left(Tape, InStr(1, Tape, ":") - 1) * NoOfTimesresidue = totalMinutes / 60

If residue > 1 Then

lastminutes = totalMinutes Mod 60

totalhours = totalhours + residue

Multiply = totalhours & ":" & lastminutes

Else

Multiply = totalhours & ":" & totalMinutes

End IfEnd Function

-------------------------------------------In excel, the cells where your time is Make those cells as TEXT. and in the next cells use it like Multiply(A1,10)

e.g if in cell A1, the time is 12:44 and you want to multiply is 10 times, you would put in cell B1

=Multiply(A1,10)

Hope it helps

Muzammil / Pakistan

Ask your question over in the Office Software Forum,

and specify if your talking about 2 hours and 44 minutes,

or 2 minutes and 44 seconds.MIKE

It's dependent on how you enter your times, if your talking 2 hours 44 minutes or 2 minutes 44 seconds and how your cells are formatted. A B C 1) 2:44:00 10 27:20:00 < 2 Hours 44 Minutes 2) 0:02:44 10 0:27:20 < 2 Minutes 44 Seconds - The leading ZERO is required.

Put the formula: =A1 * B1 in cell C1

Put the formula: =A2 * B2 in cell C2The trick is in how the cells are formatted.

Format both C1 and C2 as

Custom Format [H]:MM:SS

the square brackets around the H are required.If your talking 2 hours 44 minutes, you get the result of 27 hours 20 minute

If your talking 2 minutes 44 seconds, you get the result of 27 minutes 20 seconds.MIKE

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History