Converting imported text as dd/mm/yyyy to date as yyyymmdd

[exceltime]

I have an Excel macro which imports a csv file with all the fields specified as text. Among these fields is a field whith a date. This fields appears as dd/mm/yyyy. When it comes to copy this field to another sheet converting it to date format yyyymmdd and later sort on the fields it does not work. Help is highly appreciated. Here is the code:

 = J + 1                        'is a acounter
    Dim tdate As Date
    Dim sDate As String
    tdate = Sheets(SSource).Cells(I, ColE).Value     'from original imported file
    sDate = Format(Tdate, "yyyymmdd")
    Sheets(tName).Cells(J, ColA).Value = Tdate

J

 

[MARK858]

First question...

Why is it

Sheets(tName).Cells(J, ColA).Value = Tdate

and not

Sheets(tName).Cells(J, ColA).Value = [B][COLOR="#FF0000"]sDate[/COLOR][/B]

Second question....
Are your Windows regional date settings dd/mm/yyyy or mm/dd/yyyy?

 

[exceltime]

First question...

Why is it

Sheets(tName).Cells(J, ColA).Value = Tdate

and not

Sheets(tName).Cells(J, ColA).Value = [B][COLOR=#FF0000]sDate[/COLOR][/B]

Second question....
Are your Windows regional date settings dd/mm/yyyy or mm/dd/yyyy?

I am using Tdate to simplify the format function. The result formatting then sDate to format the result. However I have used your proposal also but it gives the same error.

My regional settings are dd/mm/yyyy

 

[MARK858]

However I have used your proposal also but it gives the same error

It wasn't a proposal, it was a question as sDate was doing nothing to change Tdate.
Try the code below.

tdate = CDate(Sheets(SSource).Cells(I, ColE).Value)     'from original imported file
sDate = Format(tdate, "yyyymmdd")
Sheets(tName).Cells(J, ColA).Value = sDate

 

[exceltime]

It wasn't a proposal, it was a question as sDate was doing nothing to change Tdate.
Try the code below.

tdate = CDate(Sheets(SSource).Cells(I, ColE).Value)     'from original imported file
sDate = Format(tdate, "yyyymmdd")
Sheets(tName).Cells(J, ColA).Value = sDate

I tried same error "Type mismatch"

 

[MARK858]

Post your full code so we can check that your variables are correct, including the variables for SSource and tName as the code doesn't err for me when they are correct.

Btw, the last post is the first post where you have mentioned that you received a "Type mismatch" error (all you stated before was "it does not work").
If you did then I would have asked for the full code in post #2.

 

[exceltime]

Post your full code so we can check that your variables are correct, including the variables for SSource and tName as the code doesn't err for me when they are correct.

Btw, the last post is the first post where you have mentioned that you received a "Type mismatch" error (all you stated before was "it does not work").
If you did then I would have asked for the full code in post #2.

Set SSource = ActiveWorkbook.ActiveSheet

tName = InputBox("Results sheet is " & CelVal, "INGSearch", CelVal) 
         If Not sheetExists(tName) Then
              Sheets.Add().Name = tName
         End If

J = J + 1
    Dim tDate As Date
    Dim sDate As String
    tDate = CDate(Sheets(SSource).Cells(I, ColE).Value)     'from original imported file
    sDate = Format(tDate, "yyyymmdd")
    Sheets(tName).Cells(J, ColA).Value = sDate

THe counter J may go 1 to 500. J is inner loop. There is a counter I in the outer loop and I may go from 1 to 4000. The statement beginning with tDate fails with error 13.

 

[MARK858]

SSource is a worksheet variable not a string variable so...

tDate = CDate(SSource.Cells(I, ColE).Value)

and I still very much doubt that is the full code as you aren't showing the variables for I, J, ColA and ColE, so if you have anymore queries I need the full code as already requested.

 

[exceltime]

SSource is a worksheet variable not a string variable so...

tDate = CDate(SSource.Cells(I, ColE).Value)

and I still very much doubt that is the full code as you aren't showing the variables for I, J, ColA and ColE, so if you have anymore queries I need the full code as already requested.

It worked like charm. Thanks a lot. I have another problem with sorting, but I'll open a separate thread for that.