# 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!

#1
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:ssFormat 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))

Report •

#2
November 12, 2009 at 05:43:46
 Hi,I see that you have entered your times into cells F8 and F10 using this format:`00:00:00` which is: min:sec:hundredths.Excel treats this entry as a time and considers it to be Hours:Minutes:SecondsWhen you added F10 to F8 in cell F12 you got 07:14:41This looks correct, but try changing F8 from 04:56:10 to 04:56:31The 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 8 ```Cell C20 calculates the total time in hundredths of a secondThe formula is:`=(C18*60*100)+(C19*60*100)+(D18*100)+(D19*100)+E18+E19`The number of times this can be accomplished in one hour is in cell C21The 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.287102046If 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.HTHRegards

Report •

#3
November 12, 2009 at 07:16:38
 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.

Report •

Related Solutions

#4
November 12, 2009 at 08:47:57
 I have always used this Custom Format:[H]:mm:ss.000Note the decimal point at the end in front of the 000You can then enter your time as:04:56.10MIKEhttp://www.skeptic.com/

Report •

#5
November 12, 2009 at 09:12:19
 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 folderEnter 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 Function```To test the function:Format a cell as TextEnter 01:01:99 in the cellIn 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

Report •

#6
November 12, 2009 at 09:45:04
 Hi,Based on Mike's suggestion, you can get your required result with the following:Format the two stopwatch input cells as mm:ss.00and the cell with the sum of the two times with the same format.Enter stopwatch times as 4:56.10 or 2:18.31The 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 MikeRegards

Report •

#7
November 12, 2009 at 15:41:21
 Glad I could help.MIKEhttp://www.skeptic.com/

Report •