I need to take time from a stopwatch (00:00:00 = min:sec:hundreths) and divide it into an hour. Cell F8 (load time - 04:56:10), Cell F10 (run time - 02:18:31), Cell F12 (equals sum of F8 & F10 - 07:14:41). Now I need to show in F14 (number format with 2 decimals) how many times I can do F12 in an hour. I guess I need what format does each cell need and the formula for F14.

First, may I suggest that you use a subject line more relevant to the contents of your post? If everyone used a subject line like "Help" or "Need Excel Help" we couldn't tell one thread from another. Thanks! That said, try this:

Format the three cells that contain times as:

Custom: h:mm:ss

Format F14 (the cell with the final result you are looking for) as a number and use this formula:

=60/(HOUR(F12)+MINUTE(F12)/60+SECOND(F12)/100)

or

=60/(SUM(HOUR(F12),MINUTE(F12)/60,SECOND(F12)/100))

Hi, I see that you have entered your times into cells F8 and F10 using this format:

00:00:00which is: min:sec:hundredths.Excel treats this entry as a time and considers it to be Hours:Minutes:Seconds

When you added F10 to F8 in cell F12 you got 07:14:41

This looks correct, but try changing F8 from 04:56:10 to 04:56:31

The result is now 07:15:02, but it should be 07:14:62The error is because the last part of your entry was in hundredths of a second and Excel did not recognize this.

There is no easy way to enter 04:56:10 or similar, in a cell and not have it recognized as a time by Excel.As a workaround I suggest the following:

Use six cells to enter the two times like this:B C D E minutes seconds hundredths 18 Load time 4 56 10 19 Run time 2 18 31 20 Total 43441 (in hundredths) 21 Per hour 8Cell C20 calculates the total time in hundredths of a second

The formula is:=(C18*60*100)+(C19*60*100)+(D18*100)+(D19*100)+E18+E19The number of times this can be accomplished in one hour is in cell C21

The formula is:=INT((60*60*100)/C20)The INT() part of the formula ensures that the result is the whole number of times that the total time can be completed in one hour. In this example the ordinary division yielded 8.287102046

If you don't like this workaround, I have written a user defined function which will convert an entry such as 04:56:10 into hundredths of a second so that you can do the addition and division using standard formulas. Let me know if you are interested in this option.

HTH

Regards

HTH, Thank you very much, everything you said makes sense. I would also love to see the other option you offered to compare which might work best for my application.

I have always used this Custom Format: [H]:mm:ss.000

Note the decimal point at the end in front of the 000

You can then enter your time as:

04:56.10

MIKE

Hi, This option uses a User Defined Function, written in Visual Basic.

The cells containing the time must be formatted as Text.

Before entering any time information in the cells, format the cells as Text (Number Tab on format).

Enter stopwatch time as 01:02:99 for example.The user defined function is entered in a standard module, attached to your workbook. This means that the function is only available when this workbook is open. As the function is so specific to what you are doing in this workbook, this should be OK, and the function will travel with the workbook if you copy and send it to anyone else.

To enter the code in 'Your.xls' workbook:

Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)

In the Project Explorer window (usually on left), find VBAProject(Your.xls)

Right click on it and select Insert, then Module (not Class Module)

Double click Module1 which is under the Modules folder

Enter the code in the main window.Option Explicit Private Function SW2Hunds(rngSW As Range) As Variant '*************************************************** 'StopWatch to Hundredths (SW2Hund) 'Function to convert text of a stopwatch time 'in the format hours (optional):minutes:seconds:hundredths 'for example 01:02:03 or 01:02:03:04 'into hundredths of a second '*************************************************** Dim strSW As String Dim dblHr As Double Dim intMin As Integer Dim intSec As Integer Dim intHund As Integer Dim dblErr As Double Dim intDelimArry(3) As Integer Dim m As Integer Dim n As Integer On Error GoTo ErrHnd '*************************************************** 'get data in referenced cell strSW = rngSW.Text 'find all :'s in the text, count them and save their positions m = 0 For n = 1 To Len(strSW) If Mid(strSW, n, 1) = ":" Then m = m + 1 intDelimArry(m) = n End If Next n 'if not at least two :'s then an error and exit If m < 2 Or m > 3 Then dblErr = xlErrValue: GoTo ErrEnd 'if no hours add zero hours and adjust position of :'s If m < 3 Then strSW = "0:" & strSW intDelimArry(3) = intDelimArry(2) + 2 intDelimArry(2) = intDelimArry(1) + 2 intDelimArry(1) = 2 End If 'get each element of the stopwatch time and convert it dblHr = CDbl(Left(strSW, intDelimArry(1) - 1)) * 60 * 60 * 100 intMin = CInt(Mid(strSW, intDelimArry(1) + 1, intDelimArry(2) - intDelimArry(1) - 1)) * 60 * 100 intSec = CInt(Mid(strSW, intDelimArry(2) + 1, intDelimArry(3) - intDelimArry(2) - 1)) * 100 intHund = CInt(Right(strSW, Len(strSW) - intDelimArry(3))) 'add all the parts together and return the result SW2Hunds = dblHr + intMin + intSec + intHund Exit Function '*************************************************** 'error end ErrEnd: SW2Hunds = CVErr(dblErr) Exit Function '*************************************************** 'Error handler ErrHnd: SW2Hunds = CVErr(xlErrNA) End FunctionTo test the function:

Format a cell as Text

Enter 01:01:99 in the cell

In another cell enter =SW2Hund(

then use your mouse to select the cell with the time in it, and finish the function with )

It will look like this:=SW2Hunds(H12)The cell should show 6199

(Format the cell with the formula as number with no decimal places)

Try 1:1:99 and it should still work, as will 0:1:1:99Use the formula twice, once on Load time and once on Run time. Sum the two values and as before use:

=INT((60*60*100)/C20)to get the number of times in one hour.This is what your original values look like:

B C D Data entry Hundredths 18 Load time 4:56:10 29610 19 Run time 2:18:31 13831 20 Total 43441 (in hundredths) 21 Per hour 8

Cells D18 to D21 contain=SW2Hunds(C18) =SW2Hunds(C19) =D18+D19 =INT((60*60*100)/D20)If you have problems with this please ask.

Regards

Hi, Based on Mike's suggestion, you can get your required result with the following:

Format the two stopwatch input cells as mm:ss.00

and the cell with the sum of the two times with the same format.Enter stopwatch times as 4:56.10 or 2:18.31

The result calculates the hundredths correctly.

If the results of adding the two times is in cell E5:

In the cell to calculate the number of times per hour use this:

=INT((1/(24*60))/E5*60)Thanks Mike

Regards

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History