Need Excel Help!

Microsoft Excel 2002 (version upgrade)
November 11, 2009 at 17:17:24
Specs: Windows XP
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.

See More: Need Excel Help!

Report •

November 11, 2009 at 17:54:50
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:




Report •

November 12, 2009 at 05:43:46

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

which 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:62

The 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		8		

Cell C20 calculates the total time in hundredths of a second
The formula is:


The number of times this can be accomplished in one hour is in cell C21
The formula is:


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.



Report •

November 12, 2009 at 07:16:38

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.

Report •

Related Solutions

November 12, 2009 at 08:47:57
I have always used this Custom Format:


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

You can then enter your time as:



Report •

November 12, 2009 at 09:12:19

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
SW2Hunds = CVErr(dblErr)
Exit Function
'Error handler
SW2Hunds = CVErr(xlErrNA)
End Function

To 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:


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:99

Use the formula twice, once on Load time and once on Run time. Sum the two values and as before use:

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

If you have problems with this please ask.


Report •

November 12, 2009 at 09:45:04

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:

Thanks Mike


Report •

November 12, 2009 at 15:41:21
Glad I could help.


Report •

Ask Question