UDF or standard IF function for date range?

I am trying to create a range from numbers in a column, and create a range. For example,

So as soon as 1934, 1935, 1936 sequence starts, it would capture 1934 and iterate through until the difference isn't 1.

The result for this example would be:

1934 - 1936.

Once I create these date ranges, I would use text to columns by the "-" delimiter to create begin and end date columns.

The difficulty in this is comparing another column and making sure the dates are based on a particular title.

So: IF(Title 1 = Title 2, (IF date 2 - date 1 = 1, increment to check date 3 - date 2 = 1, otherwise = date 1)).

I could try to nest many IF statements, but that would get nasty, plus no idea how many dates to compare per title.

Iteration seems like it would be much easier by creating a sub or UDF in VBA, but I haven't found any examples on here (or elsewhere) that may be helpful.

Solution

This will do the job, it will take cells in column F and print your output in column G. If there is a range of years (ex: 1934-1935-1936), it will put it in a single cell as a string.

Sub range_dates()
Dim cel As Range
Dim iRow As Long, n As Long
Dim title As String, nextTitle As String
iRow = 2
n = 0
For i = 2 To 15
    Set cel = Cells(i, "F")
    title = Cells(i, "E").Value
    nextTitle = Cells(i + 1, "E").Value
    diff = Cells(i + 1, "F").Value - cel.Value

    If diff = 1 And title = nextTitle Then
        firstDate = cel.Value - n
        n = n + 1
    Else
        Cells(iRow, "H").Value = cel.Value
        Cells(iRow, "G").Value = title
        If n > 0 Then
            Cells(iRow, "H") = "'" & firstDate & " - " & cel.Value
            n = 0
        End If
        iRow = iRow + 1
    End If
Next
End Sub

I do know this code is not the most optimized, but as of now it's the one i have to share. Feel free to improve it :)

EDIT: I added the title conditions and changed the output to prevent excel from changing the string to a date format