24-hour format cannot be retained when using MACRO Excel 2016

Occasional Visitor

Hello everyone,


I have an issue with the MACRO converting my time from 24-hour to 12-hour automatically.


How do you make MACRO in Excel 2016 stick with the 24-hour format?


I've tried to looked for a couple of days now but hadn't found the correct way of doing it.


I have attached my excel file here and my personal macro.


Raw fileRaw fileProcessed fileProcessed file




1 Reply

@Zyrandiel Get & Transform makes this very simple.

- Open an empty workbook

- Data tab, Get Data button, from file, from workbook

- point to your file

- CLick the sheet containing the data

- CLick Transform data button

- Use shift key and click the first two columns to select them

- From the menu choose Remove columns drop-down and select Remove other columns

- From the Remove rows drop-down select remove top rows, enter 23 and press OK

- Select first column, choose "Split column", "By delimiter". Select Space as delimiter and OK.

- Click "Close & Load...", "Close & Load to..."

- Select Table and OK.

Next time, just hit the refresh data button.