Solved: Solved : Number Formats

[aabnormal]

I am attempting to create a concatenate formula in column L though one of the fields is a date in the format of YYYYMMDD. the problem is that some some of the data is drawn from a DB and so excel does not recognize it as a Date while other fields are recognized as a date. I found the dhCNumdate function online to take the non-date strings and convert them into dates. Though now when I try to embed this in my concatenate string as below the date format is acting all weird where I used the dhCNumdate function while the otehr cells are pulling the numeric form of the date (35897) - any idea of how to correct this??

Thanks


Public Function dhCNumdate(ByVal lngdate As Long, _
ByVal strFormat As String) As Variant
' Convert numbers to dates, depending on the specified format
' and the incoming number. In this case, the number and the
' format must match, or the output will be useless.
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim fOK As Boolean
fOK = True
Select Case strFormat
Case "MMDDYY"
intYear = lngdate Mod 100
intMonth = lngdate \ 10000
intDay = (lngdate \ 100) Mod 100
Case "MMDDYYYY"
intYear = lngdate Mod 10000
intMonth = lngdate \ 1000000
intDay = (lngdate \ 10000) Mod 100
Case "DDMMYY"
intYear = lngdate Mod 100
intMonth = (lngdate \ 100) Mod 100
intDay = lngdate \ 10000
Case "DDMMYYYY"
intYear = lngdate Mod 10000
intMonth = (lngdate \ 10000) Mod 100
intDay = lngdate \ 1000000
Case "YYMMDD", "YYYYMMDD"
intYear = lngdate \ 10000
intMonth = (lngdate \ 100) Mod 100
intDay = lngdate Mod 100
Case Else
fOK = False
End Select
If fOK Then
dhCNumdate = DateSerial(intYear, intMonth, intDay)
Else
dhCNumdate = Null
End If
End Function
Sub Macro4()
Range("L1").Select
Do
Dim invconct As String
invconct = ""
invconct = invconct & "=CONCATENATE(RC[-9],"
invconct = invconct & dhCNumdate(ActiveCell.Offset(0, -8), "YYYYMMDD")
invconct = invconct & ",RC[-7],RC[-3])"
If IsDate(ActiveCell.Offset(0, -8)) = False Then
ActiveCell.FormulaR1C1 = invconct
Else: ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-3])"
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -11))
End Sub

[Oorang]

I see you marked this as solved, would you mind posting the solution so others may learn?