Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format conversion
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format conversion
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. -- Kevin Backmann "ezil" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
date format conversion
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
TEXT DATE Format Conversion | Excel Programming | |||
Conversion of date into different format | Excel Discussion (Misc queries) | |||
Date format conversion | Excel Discussion (Misc queries) | |||
Date Format - Conversion | Excel Discussion (Misc queries) |