Text to columns date issue

[Steeviee]

Hello gang,

A while ago, I found a really neat trick on this board to change data from a download
"01.09.2011"
into proper dates - 01/09/2011 - by using the data / text to columns function. How much time did that save!?

All well and good, but when I recorded this and used it in a quite long macro, it has been noted that it changes some of the dates to the US date format. i.e. the above becomes 09/01/2011. This is really maddening, especially as something in the future now looks to be 8 months overdue.

I do this twice in the macro on two tabs and worryingly, this issue seems to only be happening on on of the worksheets. The format of the resulting cells is Date - English (United Kingdom).

Can anyone help?

Here is the section of code:

datarng = Range("A" & Rows.Count).End(xlUp).Row
Range("AD13:ad" & datarng).Select
Selection.TextToColumns Destination:=Range("AD13"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=".", FieldInfo:=Array(1, 4), TrailingMinusNumbers:=True

As always, thanks in advance

I have checked the file from last week - it was running fine then! I have a template: two reports are pasted in and then the macro runs. the template is read only and so should run the same one week to the next.

 

[GTO]

Hello gang,

A while ago, I found a really neat trick on this board to change data from a download
"01.09.2011"

If the strings are consistent, have you tried using Split?

 

[Steeviee]

Hey GTO. Thanks for the response.

The strings are consistent - all are of that format.

I don't know 'split' - can you give more detail? Will it cause an issue with there being >5000 lines? I'm doing everything I can to keep the run time of the macro to a minimum.


Cheers!

 

[Weaver]

Have you tried using the replace function instead?

Sub test()
datarng = Range("A" & Rows.Count).End(xlUp).Row
Range("AD13:AD" & datarng).Replace What:=".", Replacement:="/", LookAt:=xlPart
End Sub

 

[Trevor G]

Select your column and use Find & Replace. Find . and replace with /

If you record the process you would get something like this:

Sub mcrReplace()
'Using Find and Replace to remove stop and replace with slash

'
Range("J2:J23").Select 'Change if ncessary
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("J:J").EntireColumn.AutoFit
End Sub

 

[Steeviee]

Weaver & Trevor, thanks both for your replies. Much appreciated.

I'm going to make an off line copy and give this a go. Hopefully this will work (the dates are used in calculations and so they mustn't be text fields) and I'll let you know how I get on.

This seriously not something I thought of - I was splitting the date up =LEFT(A1,2)&"/"& ... etc
then having to copy & paste special (the file is too large to have any calulations in it, hence the macro). This took a long time, hence looking for another way. It looks like you have provided yet another solution.

Cheers again.

 

[Steeviee]

Works perfectly guys. Doesn't seem to add anything to the run time. Success.

Thanks again for your help.

 

[Shrikant]

I was using this method. Find and Replace..
But end with the same problem. the day and month just got messed up.
I posted the problem 2 months ago and someone told me to use text-to-column.
But I guess it is not working either.

 

[Steeviee]

Well, I just ran the same two reports, pasted them & ran the original macro and there was no issue (using text to columns) and I had no issue with the find/replace version. I think Excel just has a bad day? Don't know.