pleas help to create a UDF

[sadath]

Hi

I have range of cells having time.
each cell showing time like 18:18-23:15

I have created a UDF to find the sum of time difference

Private Function SUMTIME(Rng As String)
Dim Ans As Double
Dim St,En As Double

For Each cl In Rng
If cl <> "" Then
St = Left(cl, 5)
En = Right(cl, 5)
Ans = Ans + (En - St)
End If
Next cl

SUMTIME = Ans
End Function

but its showing error, please help

 

[Andrew Poulsom]

Try eg:

=MOD(RIGHT(A1,LEN(A1)-FIND("-",A1))-LEFT(A1,FIND("-",A1)-1),1)

If your times are always 5 characters you can use:

=MOD(RIGHT(A1,5)-LEFT(A1,5),1)

 

[Gary's Student]

How about:

Private Function SUMTIME(Rng As [COLOR=#0000cd][B]Range[/B][/COLOR]) As [COLOR=#0000cd][B]Date[/B][/COLOR]
Dim Ans As [COLOR=#0000cd][B]Date[/B][/COLOR]
Dim St, En As [COLOR=#0000cd][B]Date[/B][/COLOR]
Ans = 0
For Each cl In Rng
    If cl <> "" Then
        St = [COLOR=#0000cd][B]TimeValue[/B][/COLOR](Left(cl, 5))
        En = [COLOR=#0000cd][B]TimeValue[/B][/COLOR](Right(cl, 5))
        Ans = Ans + (En - St)
    End If
Next cl


SUMTIME = Ans
End Function