EDIT*** I just (5 minutes after I posted it) modified this slightly because some of the code lines got chopped and pushed to the next line when i posted it. That would cause errors in Excel. I saw your thank you message, so no problem if you haven't copied it yet. Should be okay now. I'll check in with you tonight and make sure all went well.***
Well, that took a bit more work than I thought. The bad news is, there's no way to do what you want using formulas alone. Excel provides for, but essentially ignores, fractions of seconds. The good news is, there's a workaround using a custom function I just wrote for you.
Not sure if you're familiar with VBA functions, but we're going to add one to your spreadsheet. Don't let that worry you. It's simple and if you haven't used them before, you'll learn a thing or two.
First, we're not going to use the Excel time formats. So, set the format of the columns that have your daughter's swim times in them to Text. Set the column that will have the time difference in it to Number, with 2 decimal places.
Next, click Tools on the menu. Then select Macro, then Visual Basic Editor. That will take you the editor. In the editor, click Insert on the menu, then select Module.
You should now be looking at a large white area, where VBA code is typed. The only line present should be "Option Explicit".
Put the cursor under that line and press
to insert a blank line. Next, copy the code below and paster in the code window after the blank line. Note that my code is properly indented, but Yahoo Answers removes white space, so there's no indentation below. It doesn't matter to Excel, it just makes reading the code easier when it's indented. Okay, cut and paste!!
Function TimeDiff(strT1 As String, strT2 As String) As Single
On Error GoTo Err_TimeDiff
Dim varStrPos As Variant, i As Integer, dtTime As Date
Dim strTime As String, sglTime As Single, sglTime1 As Single
Dim sglTime2 As Single
If strT1 = vbNullString Or strT2 = vbNullString Then
' One or both of the times is blank. Return error.
TimeDiff = -999
GoTo Exit_TimeDiff
End If
' Loop twice to process both times passed to this functiion
For i = 1 To 2
If i = 1 Then
' Convert first time
strTime = strT1
Else
strTime = strT2
End If
' Reset
sglTime = 0
' Search for decimal point
varStrPos = InStr(strTime, ".")
If IsNull(varStrPos) Then
' No decimal found, no fractional seconds
Else
' Add fractional seconds to time variable
sglTime = sglTime + (Val(Right$(strTime, Len(strTime) - _
varStrPos)) / 100)
' Remove fractional seconds from time string
strTime = Left$(strTime, varStrPos - 1)
End If
' If time string does not include hours add them.
' It must include minutes and seconds,
Select Case Len(strTime)
Case Is < 4
' Time does not include at least 1 minutes digit,
' a colon and two seconds digits
TimeDiff = -999
GoTo Exit_TimeDiff
Case 4
' Add 2nd minutes digit and hours
strTime = "00:0" & strTime
Case 5
' Add hours
strTime = "00:" & strTime
Case 6
' Add 2nd hours digit
strTime = "0" & strTime
Case Else
TimeDiff = -999
GoTo Exit_TimeDiff
End Select
' Add hours, minutes and seconds from modified time string
dtTime = strTime
sglTime = sglTime + (Hour(dtTime) * 3600)
sglTime = sglTime + (Minute(dtTime) * 60)
sglTime = sglTime + Second(dtTime)
ProcessNextTimeValue:
If i = 1 Then
sglTime1 = sglTime
Else
sglTime2 = sglTime
End If
Next i
' Return difference in seconds
TimeDiff = sglTime1 - sglTime2
Exit_TimeDiff:
Exit Function
Err_TimeDiff:
TimeDiff = -999
Resume Exit_TimeDiff
End Function
Code ends above this line - STOP copying there.
Once you paste the code, do a File\Save.
Back in the spreadsheet, type the following formula in the column that holds the time differences (adjust the cell references if needed):
=TimeDiff(A1,B1)
You'll get the difference in seconds, including hundredths, as a positive or negative number.
One final note. If either of the swim time cells is blank, or if either doesn't contain at least m:ss, the function returns -999 as an error value. This function will work properly whether or not hours or fractions of a second are included, but will generate an error if it doesn't find at least one digit for minutes, a colon and two digits for seconds.
I spent a bit of time on this, so I hope you'll use it. You won't be disappointed. Come back at me if you have any questions.