Convert Date from YYYYMMDD Format

[chris_h_465]

Hi,

I currently import files in CSV format from an AS/400 system and the date comes through in a YYYYMMDD format e.g today would be 20090306.

I know I can correct this by using the Text to Column function however there tends to be several columns of this format and it can become quite tedious to complete on 50+ spreadsheets on a daily basis.

I know I can't change the date in our AS/400 system so Ideally I would like to automate this process using a macro/VBA but I am not sure where to start.

I have some experince of VB programming but that was several years ago so any help would be appreciated.

Thanks

Chris

[solnajeff]

HI

The code you can use to convert each value is relatively simple, e.g. in A1

Let Cells(1, 1).Value = Left(Cells(1, 1), 4) & "/" & Mid(Cells(1, 1), 5, 2) & "/" & Right(Cells(1, 1), 2)

this will automatically default to whatever date format you have set under windows.

Hopefully this will get you started.

Regards

Jeff

[chris_h_465]

HI

The code you can use to convert each value is relatively simple, e.g. in A1

Let Cells(1, 1).Value = Left(Cells(1, 1), 4) & "/" & Mid(Cells(1, 1), 5, 2) & "/" & Right(Cells(1, 1), 2)

this will automatically default to whatever date format you have set under windows.

Hopefully this will get you started.

Regards

Jeff

Thanks Jeff, I have managed to get this working on all cells in column A by creating a loop however I would like it to run over a variable selection of columns i.e it could be column C, F and AA or D, E and G etc. Can I somhow tell it to do that?

Sub ConvertDate()
A = 1
Do Until Cells(A, ).Value = ""
Let Cells(A, 1).Value = Left(Cells(A, 1), 4) & "/" & Mid(Cells(A, 1), 5, 2) & "/" & Right(Cells(A, 1), 2)
A = A + 1
Loop
End Sub

[Krishnakumar]

Hi Chris,

Assume your date in Col A. Try this

Sub kTest()
Columns(1).Insert
With Range("b2", Range("b" & Rows.Count).End(xlUp))
    .Offset(, -1).Resize(.Rows.Count).FormulaR1C1 = "=date(left(rc[1],4),mid(rc[1],5,2),right(rc[1],2))"
    .Value = .Offset(, -1).Resize(.Rows.Count).Value
End With
Columns(1).Delete
End Sub

HTH