# pleas help to create a UDF

##### Active Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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)

Code:
``````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``````

Last edited:

Replies
1
Views
226
Replies
5
Views
128
Replies
5
Views
255
Replies
9
Views
334
Replies
6
Views
204

1,207,096
Messages
6,076,555
Members
446,213
Latest member
bettigb

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back