Private Sub Worksheet_Change(ByVal Target As Range)
Dim n1 As Integer, n2 As Integer, sn As Integer, fn As Integer
Dim svalue As String

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Column < 2 Then GoTo wsexit '<== Not column B

svalue = Replace(Target.Value, " ", "")
n1 = InStr(1, svalue, "(") + 1
n2 = InStr(1, svalue, ")") - 1
svalue = Mid(svalue, n1, n2 - n1 + 1)
n1 = InStr(1, svalue, "-")
sn = Evaluate(Mid(svalue, 1, n1 - 3))
fn = Evaluate(Mid(svalue, n1 + 1, Len(svalue) - n1 - 2))
Cells(Target.Row, "C") = fn - sn + 1 '<=== days in column C

wsexit:
Application.EnableEvents = True
End Sub

"Kelly Lim" wrote:

Good day,

Mind to give me a hand on this? tried to edit the code, but i cant seems to
get the name&date to Column B instead in A and the days to be in Column C.

Thanks a lot.
Kelly

"Kelly Lim" wrote:

This solution is fantastic, which part in the code should i change? if David
Jones (1st - 12th) needs to be in Column B instead of A and the days to be
put in Column C.

Kelly

"Toppers" wrote:

Try the following which works if the format is:

David Jones (1st - 12th)

i.e. there must be brackets round the date

Right click on the worksheet tab, view code and copy the code below.

When data is entered in column A, the days will be put in column B.

HTH

Private Sub Worksheet_Change(ByVal Target As Range)
Dim n1 As Integer, n2 As Integer, sn As Integer, fn As Integer
Dim svalue As String

On Error GoTo wsexit
Application.EnableEvents = False

If Target.Column < 1 Then GoTo wsexit

svalue = Replace(Target.Value, " ", "")
n1 = InStr(1, svalue, "(") + 1
n2 = InStr(1, svalue, ")") - 1
svalue = Mid(svalue, n1, n2 - n1 + 1)
n1 = InStr(1, svalue, "-")
sn = Evaluate(Mid(svalue, 1, n1 - 3))
fn = Evaluate(Mid(svalue, n1 + 1, Len(svalue) - n1 - 2))
Cells(Target.Row, "B") = fn - sn + 1

wsexit:
Application.EnableEvents = True
End Sub