date format conversion

  • Thread starter Thread starter ezil
  • Start date Start date
E

ezil

I have date data entered in a cell as general format like 24/12/07 (in
general format left side aligned)

I have to change this date to date format. How to change this through macro?
 
You could convert the date by inserting a helper column to the right of the
date column and enter the following formula (assuming the first date is in
cell A1):

=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))

Copy down the column as far as necessary, copy the entire contents of the
formula column and then do a EDIT/PASTE SPECIAL and paste the formula results
over the original date column.

Delete the heplper column when done.
 
Assuming the data is in a1 and you're converting into b1:

With Range("b1")
.FormulaR1C1 = _
"=VALUE(CONCATENATE(MID(RC[-1],4,2),""/"",LEFT(RC[-1],
2),""/"",RIGHT(RC[-1],1)))"
.NumberFormat = "mm/dd/yy;@"
End With

Cliff Edwards
 
Back
Top