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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date?
when you get VBA involved, it interprets all dates with a US format bias. If
the string can't be interpreted that way ex: 13/10/2007, then it will use dd/mm/yyyy so that is why you see mixed results. -- Regards, Tom Ogilvy "Paul J" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date?
You can select column C and then do
Data|Text to columns Fixed width and choose dmy and finish up (and maybe reformat to the way you want) Paul J wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date?
You could also use a text formula to get the actual date...
=VALUE(CONCATENATE(MID(A2,4,2),"/",LEFT(A2,2),"/",RIGHT(A2,2))) and manipulate in vba to get what you want. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format da
Seeing as VBA doesnt handle non-US date formats in this instance, and taking
up ward376's suggestion, I will use the following: Dim RecordCounter As Long ' Number of Records to be Processed RecordCounter = 15 ' Including Header ' Reformat dd.MM.yy dates in column F into dd/MM/yyyy Excel dates in column C via column E Range("F1").Copy Destination:=Range("E1") ' Copy Header Dim i As Long For i = 2 To RecordCounter ' Fill in with Excel formulae Range("E" & i).FormulaR1C1 = _ "=DATEVALUE(CONCATENATE(LEFT(RC[1],FIND(""."",RC[1])-1),""/"",MID(RC[1],(FIND(""."",RC[1])+1),((FIND(""."",RC[1],(FIND(""."",RC[1])+1))-(FIND(""."",RC[1])+1)))),""/"",RIGHT(RC[1],LEN(RC[1])-FIND(""."",RC[1],FIND(""."",RC[1])+1))))" Next i Columns("E:E").NumberFormat = "dd/mm/yyyy;@" ' Format the column Columns("E:E").Copy ' Copy Values (lose the formulae) and Formats Columns("C:C").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Columns("C:C").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False It's a bit "intense" but it does the job. Thanks for all your help. Paul J |
Reply |
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 |