I'm not sure what your problem is, but I think this function does what you
are attempting to do with your function (plus it is a little bit
shorter<g)...

Function TimeInSeconds(BuildTime As String) As Long
Dim X As Long
Dim Factors As Variant
Dim Parts() As String
Factors = Array(1, 60, 3600, 86400)
Parts = Split(BuildTime, ":")
For X = 0 To UBound(Parts)
TimeInSeconds = TimeInSeconds + Factors(X) * Parts(UBound(Parts) - X)
Next
End Function

Rick


"Russ Jones" wrote in message
...
I seem to have an inexplicable error which I hope someone can help me
solve.
I am using Excel 2003 running on a Windows XP Professional platform.

I wrote the following user-defined function to convert to seconds an
elapsed
time stored as a text string, BuildTime, which has the format dd:hh:mm:ss.
Only the necessary positions in the string are filled, i.e., one minute is
stored as 1:00, ten minutes as 10:00, one hour as 1:00:00, ten hours as
10:00:00, one day as 1:00:00:00, ten days as 10:00:00:00, etc. Values of
less than one minute are stored as 0:ss. Therefore, one and only one of
the
procedure's "If StringLen =" tests is true for any value of BuildTime.

The problem is that the procedure invariably returns a #VALUE! error when,
and only when, StringLen = 8, and I am a completely baffled as to why.
Any
help would be greatly appreciated!

Dim Days As Integer
Dim Hours As Integer
Dim Minutes As Integer
Dim Seconds As Integer
Dim StringLen As Integer

Function TimeInSeconds(BuildTime As String)
Days = 0 'Initializing these variables makes no difference; I
was
Hours = 0 'merely trying everything I could think of.
Minutes = 0
Seconds = 0
StringLen = Len(BuildTime)
If StringLen = 11 Then
Days = Val(Mid(BuildTime, 1, 2))
Hours = Val(Mid(BuildTime, 4, 2))
Minutes = Val(Mid(BuildTime, 7, 2))
End If
If StringLen = 10 Then
Days = Val(Mid(BuildTime, 1, 1))
Hours = Val(Mid(BuildTime, 3, 2))
Minutes = Val(Mid(BuildTime, 6, 2))
End If
If StringLen = 8 Then
Days = 0
Hours = Val(Mid(BuildTime, 1, 2))
Minutes = Val(Mid(BuildTime, 4, 2))
End If
If StringLen = 7 Then
Days = 0
Hours = Val(Mid(BuildTime, 1, 1))
Minutes = Val(Mid(BuildTime, 3, 2))
End If
If StringLen = 5 Then
Days = 0
Hours = 0
Minutes = Val(Mid(BuildTime, 1, 2))
End If
If StringLen = 4 Then
Days = 0
Hours = 0
Minutes = Val(Mid(BuildTime, 1, 1))
End If
Seconds = Val(Right(BuildTime, 2))
TimeInSeconds = (Days * 86400) + (Hours * 3600) + (Minutes * 60) +
Seconds
End Function