PDA

View Full Version : Date Format



Conor
06-23-2008, 02:30 AM
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
06-23-2008, 02:41 AM
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
06-23-2008, 03:05 AM
It is



Activecell.NumberFormat = "yyyymmdd"

marshybid
06-23-2008, 03:18 AM
It is



Activecell.NumberFormat = "yyyymmdd"

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 :rotlaugh: ).



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
06-23-2008, 03:35 AM
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,"-","")