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