how can I convert a number yyyymmdd to a date?

Thanks guys.
Great minds....
Would it be possible to change original column with a macro.
(I have several columns with this format that I would like to change)

you dont need a macro; just copy the formula over to the right of the yyyymmdd

Thanks, I realise that.
I receive the data from a source that always presents data in this format.
Their worksheet contains 10 columns of dates.
If I had some base code I could do the rest
Thanks
Des

spar-kle,
You can use Text To Column to convert every column by selecting the cells (one column at a time) as I showed you above.

Try this. Change the range according to your needs.

Sub SetToDate()

Dim rngRange As Range
Dim rngCell As Range
Dim strValue As String
Dim strYear, strMonth, strDay As String

'define any range to convert dates
Set rngRange = Worksheets("Sheet1").Range("I7:I10")

'loop through cells of range and set to date
For Each rngCell In rngRange
    If rngCell.Value <> Empty Then
        strValue = rngCell.Value
        'make format General before you paste the formula
        rngCell.NumberFormat = "General"
        strYear = Left(strValue, 4)
        strMonth = Mid(strValue, 5, 2)
        strDay = Right(strValue, 2)
        'set formula
        rngCell.Formula = "=DATE(" & strYear & "," & strMonth & "," & strDay & ")"
    End If
Next rngCell

End Sub

Select allOpen in new window

Here is jimyX' suggestion turned into a short macro:

Sub YMDtoDate()
With Selection.Columns(1)
    .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, FieldInfo:=Array(1, 5)
    .NumberFormat = "mm/dd/yyyy;@"
End With
End Sub

Select allOpen in new window

byundt's solution looks neat!
A lot less code and therefor much better!

Brad!!!!! those were the only points I had this month and now you have taken them away!!!!

No worries. Good to see you in action. I was particularly impressed (in a recent question) with how you detected that duplicates were throwing off the correct count of licences or whatever. I hope that question is also reopened.

Cheers

Saqib

Doh! I messed up the points again, SORRY!!!!!!
I meant ti give points to JimyX

Thanks for all your help!!
The truth is, that I wasn't sure where the feature that Jimyx referred to was located.
I have since found it on the Data menu and I can see that this would be a very useful feature in various scenarios.
Des

Des,
Thanks for having been so understanding about the point split.

The people who helped in this question do so in many others in the Excel Zone. Fairness in the points distribution goes a long way towards making us feel we are all working together towards a goal of helping you, the Asker.

Brad

Thanks Brad and Des.