Excel Problem Years Problem?

Okay...

Here is an event handler that will convert your example data into the exact result you indicate in the 'after' example. This assumes the data is in column A, beginning in row 1. It will use as many columns as the 'date range' dictates. If you have three years, columns A:D will be populated - four years will result in columns A:E returning values.

If your data does not begin in row 1, change the 'For i = 1 to LastRow' line to the number of the first row you wish to evaluate, i.e. 'For i = 5 to LastRow', etc.

Then, copy the event handler to the clipboard (highlight the entire code, right click in the highlighted area, and 'Copy'):

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim i, j, k, LastRow, var1, var2, numYrs

LastRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next

For i = 1 To LastRow

var1 = Left(Cells(i, "A"), Application.Find("-", Cells(i, "A")) - 1)

var2 = Mid(Cells(i, "A"), Application.Find("-", Cells(i, "A")) + 1, 2)

numYrs = (var2 - var1)

k = 0

For j = 0 To numYrs

If Left(var1, 1) = 0 And (var1 + k) < 10 Then

Cells(i, "A").Offset(0, j + 1).Value = 200 & var1 + k

ElseIf Left(var1, 1) = 0 And (var1 + k) > 9 Then

Cells(i, "A").Offset(0, j + 1).Value = 20 & var1 + k

End If

If Left(var1, 1) > 4 Then

Cells(i, "A").Offset(0, j + 1).Value = 19 & var1 + k

ElseIf Left(var1, 1) < 5 Then

Cells(i, "A").Offset(0, j + 1).Value = 20 & var1 + k

End If

k = k + 1

Next

Next

End Sub

Select the worksheet containing your years and right click the sheet tab.

Select 'View Code'.

Paste the event handler into the editing area to the right (right click in the area and 'Paste').

Close the VBE (red button w/white 'x').

To extract the list of years, simply double click any cell. If more data is added in the future, simply double click again to create a revised list.

Note: since 'years' are 'continuous', the turn of the century must be accounted for. The above code assigns the break point at 1950. Years 50-53 will display as 1950, 1951, 1952, 1953. Years 48-50 will display as 2048, 2049, 2050.

My solution will extract the 4 digit years from the input range but to get the 2 digit years on the same row is more complex. Here goes

Col A are date ranges

B2 =VALUE(C2-D2)

C2 =IF(VALUE(RIGHT($A2,2))>=50,

VALUE(CONCATENATE("19", RIGHT($A2,2))),

VALUE(CONCATENATE("20", RIGHT($A2,2))))

D2 =IF(VALUE(LEFT($A2,2))>=50,

VALUE(CONCATENATE("19", LEFT($A2,2))),

VALUE(CONCATENATE("20", LEFT($A2,2))))

E2 =IF(COLUMNS($A:A)>$B2,"", D2+1) copy across a few cells to accommodate years

Select B2 thru column "n" chosen and copy down to match col A.

The 50 in C2 and D2 is the year chosen to be the cutoff point for 19xx years ie any number less than 50 will become 20xx years, change if required to suit. Hide cols B and C if required.