VBA: importing date format problems

Can anyone help me.

I have to import data from various General Practice computer systems to help UK GPS analyse their patients. The data is held on .csv files on floppy disks.

The date formats are general EDIFACT style ie 19970101. It is better to import theses dates and convert to excel 1990 dates as this format (35431 or 1/1/1997).
is easy to present data in

This bit is easy: the following macro imports from a floppy and formats the correct columns to year month day.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;A:\CHDREP1.CSV", _
Destination:=Sheets("upload").Range("A1"))
.Name = "CHDREP1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False

'it is important the the below row match, 5= date, 1 = number.
'................................a b c d e f g h i j k l m n o p q r s t u
.TextFileColumnDataTypes = Array(1, 5, 1, 1, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5, 1, _
5, 1, 5, 1, 5, 1, 5, 1, 5, 1, 5)
'v w x y z a b c d c d

The problem is that in some systems when only the year of a diagnosis is known 1997 is represented as 19970000 and the dates corrupt as in the example attached. The only way I can think of to correct this is to write a macro to search all the above dates at the end and if the dates are > 19000000 (ie corrupted) then perform the following function
(c.value-19000000)*0.3653 on the squares.

How would you correct the data on the attached spreadsheet (which is an example of what the dates look like after importing.

thanks

Dr Robin H

thanks tom.

The following VBA was the solution that I found.

Sub datecorrects()

Dim c As Variant

'

Range("e1:ao1200").Select

Set rangetoalter = Selection
For Each c In rangetoalter

'0.03653 chosen as this is slightly more than a leap year and ensures
'that date will be in the correct year.

If IsNumeric(c.Value) And Right(c.Value, 1) = 0 And c.Value > 19000000 Then
c.Value = (c.Value - 19000000) * 0.03653
End If
'the macro will crash if on a date formatted square. Also some columns have pathology results in thus the number has to be greater than 1900. The error has occured if the right digit is a 0

Next c
End Sub

i am sure this is not very elegant but it works