With data as you describe, in column`C`

enter the list of customer numbers. In cell `D1`

enter the *Array Formula*:

```
=MIN(IF(B$1:B$24=C1,A$1:A$24,""))
```

and then, copy down *(this is because the oldest date will have the smallest value)*:

**Array formulas** must be entered with `Ctrl` + `Shift` + `Enter` rather than just the `Enter` key.

Column `D`

should be given a *Date* number-format.

If all that you really need is the Year, then use @robinCTS formula.

IF you want the result to be "real Excel Dates" and

- if your data is TEXT and not real dates,
- and if your regional date settings are
`DMY`

,
- and the column is formatted as
`dd-mm-yyyy`

then you could use this formula:

```
=IFERROR(IFERROR(IFERROR(DATEVALUE(A2),DATEVALUE(A2&"-01")),DATEVALUE(A2&"-01-01")),"illegal date format")
```

Note that dates prior to 1900 cannot be real dates in Excel, but could be represented as TEXT strings. I will leave it to you to tweak the formula.

If your output needs to be internationally aware to countries that have other date formats, then a VBA solution might be best. If that is not possible, helper columns to split the data and then reassemble appropriately, or a very complex formula to do the same, could be used.

Note that this UDF will output either the actual date, or just the year, depending on the second optional argument.

```
Option Explicit
Function ConvertToDate(S As String, Optional Yr As Boolean = False) As Variant
Dim V
Dim dtTemp As Date
V = Split(S, "-")
Select Case UBound(V)
Case 0
dtTemp = DateSerial(V(0), 1, 1)
Case 1
dtTemp = DateSerial(V(0), V(1), 1)
Case 2
If Len(V(0)) = 4 Then
dtTemp = CDate(S)
Else
dtTemp = DateSerial(V(2), V(1), V(0))
End If
End Select
If Yr = True Then
ConvertToDate = Year(dtTemp)
Else
If Year(dtTemp) < 1900 Then
ConvertToDate = Format(dtTemp, "dd-mm-yyyy")
Else
ConvertToDate = dtTemp
End If
End If
End Function
```

Both methods look at the first segment, and assume it is a year if `LEN > 4`

, and then look at how many segments to decide whether to append the necessary `-01`

's

The VBA solution will output dates prior to 1900 as a text string.

## Best Answer

You can do this with

`INDEX`

and`MATCH`

. I'm assuming your date columns are sorted from oldest to newest. To find the first date after`A2`

in`B2:B30`

, use the following formula.The key is that

`MATCH(A2,B2:B30,1)`

will return the index of the most recent date in`B2:B30`

that matches or is before the date in`A2`

. Since your dates are sorted, the next date in the list will occur after the date in`A2`

, so adding 1 to this index will give you what you want.