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]

Is it actually changing the date values, or just the formats the dates are shown in?
Because you can choose whatever format you want to apply to the column after running the Text to Columns on that data.

If it is actually changing the value, please post some samples of what your data looks like.

 

[ChrisFoster]

From 30th September the next date should be 4th October, but it is changing the format to 10th April.
It is changing the format on any date it can do - as an example as there are not 23 months in year it keeps 23/09/2021, but changes 04/10/2021 to 10/04/2021.

Date of MOC

23/09/2021

23/09/2021

24/09/2021

24/09/2021

24/09/2021

24/09/2021

29/09/2021

30/09/2021

30/09/2021

10/04/2021

10/05/2021

10/07/2021

10/07/2021

10/07/2021

10/11/2021

10/11/2021

14/10/2021

14/10/2021

 

[Joe4]

I do not see any time stamps on your data?
What does the data look like BEFORE you run the code on it?
Are some formatted as text, and others date/time BEFORE your run the code (usually is evident, as some of the data will be left-justified and other will be right-justified).

If all the data is initially formatted as text, this code works for me, if the date part of the data was in the format that you show.

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

The "4" in the Array(0, 4) part of the code tell it that the date is in DMY format, so it should be converting it correctly.

However, I have seen some weird things happen for people using the European version of Excel (I am using the US one).
I think that is because even though your Regional Settings may reflect the European standards, VBA still uses the US standards.
(And that makes it really hard for people like me to recreate the exact behavior you may be experiencing).

There are other ways we could get the date, by using some formulas instead.

 

[ChrisFoster]

Yeah it does seem that VBA is using US format rather than UK - when I do the text to columns manually it works fine, but only when running it through VBA it goes wrong.

Below is before and after comparison.

Before	After
23/09/2021 11:30:00	23/09/2021
23/09/2021 10:30:00	23/09/2021
24/09/2021 10:00:00	24/09/2021
24/09/2021 11:00:00	24/09/2021
24/09/2021 10:00:00	24/09/2021
24/09/2021 10:30:00	24/09/2021
29/09/2021 10:00:00	29/09/2021
30/09/2021 10:30:00	30/09/2021
30/09/2021 10:00:00	30/09/2021
30/09/2021 10:00:00	30/09/2021
05/10/2021 10:00:00	10/05/2021
07/10/2021 10:30:00	10/07/2021
11/10/2021 10:30:00	10/11/2021
07/10/2021 11:00:00	10/07/2021
07/10/2021 10:00:00	10/07/2021
11/10/2021 10:00:00	10/11/2021
14/10/2021 10:00:00	14/10/2021
14/10/2021 10:30:00	14/10/2021

 

[Joe4]

If the values are strings, then this formula would pull the date out the way you want (for a string in cell A1):

=DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2))

Does that return what you want?

If you need it in VBA, there are ways we can automate this, if you provide us more of the details (i.e. what row to start in, do you want Date in column F, time in column G, etc).

 

[ChrisFoster]

I have used that formula but changed the A1 to F2 (as below)

=DATE(MID(F2,7,4),MID(F2,4,2),LEFT(F2,2))

But get the below result.

Before	After
24/09/2021 10:00:00	13/04/4796

 

[Joe4]

So it sounds to me like your entries are NOT text, but rather they are already entered as valid date/time values.

If that is the case, try this formula instead to get the date portion, and format in your desired date format:

=INT(F2)

 

[Akuini]

Hi, @ChrisFoster
Try this:

Sub flipDate()
'If the first two number is < 13, then it will swap the day & month.
Dim x As String
Dim r As Range
Application.ScreenUpdating = False
For Each r In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    x = r.Text
    If CLng(Left(x, 2)) < 13 Then
        r.Offset(, 1).Value = DateSerial(Mid(x, 7, 4), Left(x, 2), Mid(x, 4, 2))
    Else
        r.Offset(, 1) = CDate(Left(x, 10))
    End If
Next
Application.ScreenUpdating = True
End Sub

 

[ChrisFoster]

So it sounds to me like your entries are NOT text, but rather they are already entered as valid date/time values.

If that is the case, try this formula instead to get the date portion, and format in your desired date format:

=INT(F2)

This has helped massively.
I've managed to do it without text to columns - this is my new code which works perfectly.

    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=INT(RC[-1])"
    Selection.AutoFill Destination:=Range("G2:G" & Range("A" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
    Columns("G:G").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    
    Range("F1").Select
    Selection.Copy
    Range("G1").Select
    ActiveSheet.Paste
    Columns("G:G").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft