VBA Change Date from European

**Jmarsh4** · 07-30-2012, 12:48 PM

Hello,

I am writing a macro in which I need to change two columns of dates from european format to standard and change the periods to dashes. The dates currently look like 30.06.2012 and I need them to appear as 06/31/2012. In recording this change I see that I can use:

Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True

but the goal is to have an extremely clean, short macro that other users can easily troubleshoot. I've been able to simplify most other functions down to one line of code. Is there a simpler way to do this?

Thank you!

**venkat1926** · 07-31-2012, 01:34 AM

why do you need a macro for thsis

suppose A1 has entry 30.06.12
in B1 type this formula
=(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))*1
copy B1 down.
if you do not ant col. A delete that colum

**Jmarsh4** · 07-31-2012, 02:34 PM

It is part of a larger macro that is designed to enable users to paste the raw data in and have the completed report come out with the push of a button.

**Cutter** · 07-31-2012, 09:40 AM

@ Jmarsh4

Please notice that code tags have been added to your post. You've been a member long enough to know that the forum rules require them so please remember to add them whenever showing code in any of your future posts. To see instructions for applying them, click on the Forum Rules button at top of the page and read Rule #3.
Thanks.