Hi Stuart €“

The procedure below converts either serial dates or string dates to serial
dates (and optionally to strings) in €˜ddmmyy format. Note the following:

1. If your source dates are text strings, you have to tell the procedure how
the date is constructed by modifying some variables in the declarations
section. Otherwise, date functions (datevalue or dateserial) cant
consistently interpret year, month, or day.

2. The procedure replaces the dates €œin-place€� so test it on dummy data.
Or, modify the column €˜OffSet to put the results in an empty column.

3. You mentioned that your €˜system accepts only numeric values. Dates are
numeric, so I assumed you mean numeric digits. Therefore, the procedure
optionally converts dates to strings that contain only numeric digits.

Sub convertDatesInPlace()
'----------------------------------------------------------------
'If dates are text strings, set the values in next 3 statements.
'If dates are true excel serial dates, no modification is needed.
' Enter the within-string order for (m)onth, (d)ay, and (y)ear.
' Enter the date delimiter used.
' Enter number of digits in the year portion (2 or 4).
m = 1: d = 2: y = 3
delimiter = "-"
yeardigits = 2
Set dateRange = Range("A1:A100") '<====adjust input range to suit.
'-----------------------------------------------------------------
'1 is subtracted for split array index
m = m - 1: d = d - 1: y = y - 1
For Each dt In dateRange
On Error GoTo invalidDate
With dt.Offset(0, 0) '<==adjust column offset for results
If IsDate(dt) Then
.NumberFormat = "ddmmyy"
If WorksheetFunction.IsNumber(dt) Then
'Cell contains serial date
.Value = DateValue(dt)
Else
'cell contains valid textual date string, but must be
'converted to true excel serial date before formatting
'because the DateValue function may misinterpret date.
parts = Split(dt, delimiter)
mnth = Val(parts(m))
dy = Val(parts(d))
yr = Val(parts(y)) + IIf(yeardigits = 2, 2000, 0)
.Value = DateSerial(yr, mnth, dy)
End If
End If
End With
invalidDate:
On Error GoTo 0
Next 'dt

'Optional conversion of serial dates to text
If MsgBox("Output dates as text?", vbYesNo) = vbYes Then
For Each dt In dateRange
If IsDate(dt) Then
With dt.Offset(0, 0)
.NumberFormat = "@"
.Value = Format(Day(dt), "0#") & _
Format(Month(dt), "0#") & _
Right(Year(dt), 2)
End With
End If
Next 'dt
End If
End Sub

-----
Jay