Text to columns date formatting issue

Question

I'm currently having an issue trying to get a column which contains time and date minutes and seconds info to be reformatted into a time and date format I can then use with the filters to re-order oldest to newest etc.

here is an example of the cell entry

24/03/2021 10:30:35:540

Now, if I use the Macro recorder to record me selecting the desired column and using text to columns feature > Fixed width, take the column break line to 16 and select General for column data format, I end up with the cell entry as this below, which works with filtering.

24/03/2021  10:30:00 AM

once stop recording the Macro and review I get this below in VBA:

Columns("P:P").Select
    Selection.TextToColumns Destination:=Range("P1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(16, 1)), TrailingMinusNumbers:=True

My issues is, when I try to run this code as a macro, it affects the dates, see explanation below, it seems to only affect dates where the format can be swapped to Americanised?

so for example original cell contains 7th March 2021

07/03/2021 05:46:45:328

When manually using text to columns, its ok, but when using VBA running above code, date and month transposes, below is results split into two columns, 3 and 7 have swapped, and now date is 3rd July

3/07/2021 5:46  :45:328

I don't really need to use text to columns feature if I don't have to, but having trouble getting the cell to convert to date and time format without it.

For example have also tried =LEFT(P2,16) then re-pasted the columns as values then tried to use format cells > custom > d/mm/yyyy h:mm, but that didn't work.

if anyone has any other suggestions I'm keen to hear?, hoping to be able to run something through VBA as part of a larger set of Macro's to focus is to be able to get it done via VBA.

Siddharth Rout · Answer 1 · 2021-03-29T04:13:08.820

0

Change Array(0, 1) to Array(1,4) for DMY

If you record the macro with the below setting you will get the above code.

Also you do not need to SELECT the column. Try this

Columns("P:P").TextToColumns Destination:=Range("P1"), _
DataType:=xlFixedWidth, FieldInfo:=Array(Array(1,4), Array(16, 1)), _
TrailingMinusNumbers:=True

Note: This unfortunately is not 100% reliable. VBA is "very US-centric" :D

edited Mar 29 '21 at 04:13

answered Mar 29 '21 at 04:06

Siddharth Rout

147,039
17
206
250

Have just changed it and it seems to retain the same issue where it transposes the date around, it also looked to have cut too much from the hh:mm details as well, may be due to how the dates are entered with no 0 for first 9 days of month just 7/3/2021. – Rank_Amatuer_VBA Mar 29 '21 at 04:51
Format the columns and then use `.TextToColumns`. See if that helps? – Siddharth Rout Mar 29 '21 at 05:18
Hi Siddharth, thanks for the extra suggestion, looks to still revert back to usa format even with a format change prior to running the text to cols VBA macro, thanks for assistance and hints anyway! – Rank_Amatuer_VBA Mar 30 '21 at 06:38

score 0 · Answer 2 · answered Mar 29 '21 at 11:12

Consider a Power Query solution (and, if your data is originally coming from a CSV file, this algorithm could be incorporated and used as part of your import procedure).

Retain the fractional seconds by replacing the final colon with a decimal:
- Split on the last : colon

Then Merge (rejoin) using the . decimal as the separator

Change data type using locale and selecting something like English(European) and datetime
- Access this dialog from right click on the column

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Timestamp", 
            Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, true), {"Timestamp.1", "Timestamp.2"}),
    #"Merged Columns" = Table.CombineColumns(
            Table.TransformColumnTypes(
                #"Split Column by Delimiter", {{"Timestamp.2", type text}}, "en-US"),
                    {"Timestamp.1", "Timestamp.2"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Timestamp"),
    
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Merged Columns", {{"Timestamp", type datetime}}, "en-150")
in
    #"Changed Type with Locale"

Hi Ron, thanks for your suggestion, will give it a go when I get the chance and see how it goes! — Rank_Amatuer_VBA, Mar 30 '21 at 06:40

Text to columns date formatting issue

2 Answers2