VBA - Text to columns, date format

[ChrisFoster]

Hi all,

I am using the below code to remove the time stamp on a column of dates (I just want the date not the time as well).
But it formats in to US Date format and not UK, even though the original format is UK. It's driving me mad.

Does anyone know how to do this but keep the date in UK format?

    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").Select
    Selection.TextToColumns Destination:=Range("F1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 4), Array(10, 1)), TrailingMinusNumbers:=True

 

[Joe4]

Excellent!

Glad you got it working the way you need.

The key to understanding the method I used is that Excel actually stores days as numbers, specificially the number of days since 1/0/1900, and time is just the fractional part of one day.
So to remove the "time" piece of a valid date/time entry, we can use the INT function to strip the fractional piece.