Date Format

[Conor]

Hi Guys,
I have a report that is exported into excel. In one cell is have a date like "As at 23-06-2008". What i want to know is how to convert this to yyyymmdd format

[choubix]

in excel: right click on the cell, format cell, selec "number" and then "custom".
here you can change the format

I don't know the code in vba but I think the macro recorder can be a good poitner in this case

[Bob Phillips]

It is

[vba]

Activecell.NumberFormat = "yyyymmdd"
[/vba]

[marshybid]

It is

[vba]

Activecell.NumberFormat = "yyyymmdd"
[/vba]

Hi Conor,

xld kindly provided me with a piece of code recently which deleted unwanted rows of data.

I have edited that code to replace the "as at " in your cells with "", then format the cells as "yyyymmdd"

This code does however assume that all of the cells containing this data are in one column (in the code below that is column 4)

xld, thought this might be another way to use your original code (although you will no doubt know of a better way than this ).

Sub ChngFormatUsingAutofilter()
     '-----------------------------------------------------------------
    Const TestColumn As Long = 4 ''<==== Change this to whichever column contains your data
    Dim cRows As Long
     
     'first, count the rows to operate on
    cRows = Cells(Rows.Count, TestColumn).End(xlUp).Row
     
     'finally, apply the autofilter for all matching cells
    Columns(TestColumn).AutoFilter Field:=1, Criteria1:="=*as at*", Operator:=xlAnd
     
     'we now have only matching rows visible, so we can
     'format these matching rows
    With Cells(2, TestColumn).Resize(cRows - 1)
        .SpecialCells(xlCellTypeVisible).Replace What:="as at ", Replacement:=""
        .SpecialCells(xlCellTypeVisible).NumberFormat = "yyyymmdd"
    End With
     
    Columns(TestColumn).AutoFilter
End Sub

Marshybid

[Simon Lloyd]

If its actually text rather than a date you can use this in another column and copy down to change the dates to the format you want:
=SUBSTITUTE(A1,"-","")