macro to convert YYYYMMDD into DD/MM/YYYY

Use:

Range("I18") = Evaluate("=DATE(LEFT(H18,4),MID(H18,5,2),RIGHT(H18,2))")

Originally Posted by Vogelmann

thank you, I see that the word Èvaluate is missing in my code - what does this word exactly mean here?

Evaluate Method converts a Microsoft Excel name to an object or a value

Here's what it can do:

1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process!

Try the below code. This will work in all dates in Column F (starting F11 and going all the way down). The results will be in DD/MM/YYYY format in adjacant cell (Column E)

Sub Dates()
Dim R As Range
Range("F11", Range("F" & Rows.Count).End(xlUp)).Select
For Each R In Selection
R.Offset(0, 1) = Left(R, 4) & "/" & Mid(R, 5, 2) & "/" & Right(R, 2)
Next R
End Sub

Where are you pasting the code? In the respective sheet or any module?

This code is going to work in the sheet containing the data only.

Are you able to upload a sample file (post removing any sensitive data)? As the code runs absolutely fine for me when i try.

Thanks.

I pasted the below code into "Sheet 1" and it worked just fine (slightly modified to factor in 2 columns and pasting the results in Columns H and I respectively:

Sub Dates()
Dim R As Range
Range("F11", Range("F" & Rows.Count).End(xlUp)).Select
For Each R In Selection
R.Offset(0, 2) = Left(R, 4) & "/" & Mid(R, 5, 2) & "/" & Right(R, 2)
Next R
Range("G11", Range("G" & Rows.Count).End(xlUp)).Select
For Each R In Selection
R.Offset(0, 2) = Left(R, 4) & "/" & Mid(R, 5, 2) & "/" & Right(R, 2)
Next R
End Sub

I also tried my pasting it into a Module and when i run the macro while being in Sheet 1 it again works fine.

it works!!
I changed the letter from R to z, and I did not know that R.Offset must be changed to z.offset, that`s all

how to make the data stay in the same column? i.e. if I originally have the data in columns F and G, I still want the output to be in the same columns instead of H and I.
Note: changing z.offset(0,2) to z.offset(0,0) does not do the job

Glad it worked. Initial observation points that you had used R as a variable already hence it was not working (my guess). Regarding your point to have the results in the same column, please see below slightly modified code:

Sub Dates()
Dim Z As Range
Range("F11", Range("F" & Rows.Count).End(xlUp)).Select
For Each Z In Selection
Z = Left(Z, 4) & "/" & Mid(Z, 5, 2) & "/" & Right(Z, 2)
Next Z
Range("G11", Range("G" & Rows.Count).End(xlUp)).Select
For Each Z In Selection
Z = Left(Z, 4) & "/" & Mid(Z, 5, 2) & "/" & Right(Z, 2)
Next Z
End Sub

Note, that i have changed all variables from R to Z as you mentioned that Z had worked for you earlier.

Just realized that there is no need to have the loop twice, by selecting both columns in once single instance.

Sub Dates()
Dim Z As Range
Range("F11", Range("G" & Rows.Count).End(xlUp)).Select
For Each Z In Selection
Z = Left(Z, 4) & "/" & Mid(Z, 5, 2) & "/" & Right(Z, 2)
Next Z
End Sub

No worries. Please could you mark the thread as solved in case your queries have been addressed.