dates issues after web page copy

[SQUIDD]

Hi All

So i pull data into excel from the web and i dont know how to go about fixing the problem.

1, as the data comes in from the web page
or
2 try and convert it after
or
3 try to diable date recognition some how

the look like this, they are in date order, newest at the top, but as you can see, excel has other ideas, i think i have marked the bad ones in red.
it would appaer all the ones that say 2016 are mm/dd/yyyy and should be dd/mm/yyyy

thanks for looking
dave

21/12/16

17/12/16

12/02/2016

27/11/16

20/11/16

11/10/2016

16/09/16

09/09/2016

09/04/2016

25/08/16

18/08/16

08/06/2016

31/07/16

16/07/16

07/02/2016

22/06/16

06/08/2016

29/05/16

21/05/16

<tbody>
</tbody>

 

[SQUIDD]

ok

so it is is the ones with 2016 at the end that are the wrong way around.
is there a way to do it in code where it loops through the range to fix/swap them.

like, for all in range with 4 digits after the 2nd / swap the MM and DD?

thanks

dave

 

[SQUIDD]

i managed a workaround

format the column to TEXT, then used text to columns.
then before downloading new data, format the column to text again
seems to have worked.
but not an ideal solution

Dave

 

[SQUIDD]

ok, so im quite happy now with what i have done.

but can this be made and more tidy.

Sub text_to_columns()

For n = 1 To 6

    Sheets("TRAP" & n).Select
    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 4), TrailingMinusNumbers:=True
    Columns("A:A").EntireColumn.AutoFit
    
Next n

Sheets("app").Activate
End Sub