Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date?
I have inherited worksheets full of business data showing date fields stored
as Text in "dd.MM.yy" (a sort-of UK) date format. If I manually use the Edit, Replace [Ctrl+H] function on the Worksheet Column to change "." to "/", and I specify the "dd/MM/yyyy" date format for output, I can achieve just the results I require. And if I record a Macro whilst performing this manual exercise (above) it produces code like this: Columns("C:C").Select Application.ReplaceFormat.NumberFormat = "dd/MM/yyyy;@" Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False . . . . . . which looks very promising. HOWEVER when this VBA code is run it produces some very mixed results! - some dates (such as 12.06.07 [i.e. 12 Jun 2007] it converts in MM/dd/yyyy - some dates (such as 27.02.07 [i.e. 07 Feb 2007] it converts to dd/MM/yy - and the cell is marked to show that it contains a "Text Date with 2-digit Year" Help! Can anybody advise me, please? Paul J |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text "20100315" to date format | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
how i convert "100" to "hundred"( number to text) in excel-2007 | Excel Worksheet Functions | |||
Convert text "date" (DDMMMYY) to datetime value of YYYY-MM-DD - ho | Excel Discussion (Misc queries) | |||
Option Explicit and Date Format "dd mmm yyyy"? | Excel Programming |