Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-737500%22%20slang%3D%22en-US%22%3E24-hour%20format%20cannot%20be%20retained%20when%20using%20MACRO%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-737500%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20issue%20with%20the%20MACRO%20converting%20my%20time%20from%2024-hour%20to%2012-hour%20automatically.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20do%20you%20make%20MACRO%20in%20Excel%202016%20stick%20with%20the%2024-hour%20format%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20to%20looked%20for%20a%20couple%20of%20days%20now%20but%20hadn't%20found%20the%20correct%20way%20of%20doing%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20my%20excel%20file%20here%20and%20my%20personal%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122151i00931B8233C985E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%221%20Raw.JPG%22%20title%3D%221%20%20Raw.JPG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3ERaw%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122152i3CE980896ADE765B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222%20Processed.JPG%22%20title%3D%222%20%20Processed.JPG%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EProcessed%20file%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EZ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-737500%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-738617%22%20slang%3D%22en-US%22%3ERe%3A%2024-hour%20format%20cannot%20be%20retained%20when%20using%20MACRO%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-738617%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F370895%22%20target%3D%22_blank%22%3E%40Zyrandiel%3C%2FA%3E%20Get%20%26amp%3B%20Transform%20makes%20this%20very%20simple.%3C%2FP%3E%0A%3CP%3E-%20Open%20an%20empty%20workbook%3C%2FP%3E%0A%3CP%3E-%20Data%20tab%2C%20Get%20Data%20button%2C%20from%20file%2C%20from%20workbook%3C%2FP%3E%0A%3CP%3E-%20point%20to%20your%20file%3C%2FP%3E%0A%3CP%3E-%20CLick%20the%20sheet%20containing%20the%20data%3C%2FP%3E%0A%3CP%3E-%20CLick%20Transform%20data%20button%3C%2FP%3E%0A%3CP%3E-%20Use%20shift%20key%20and%20click%20the%20first%20two%20columns%20to%20select%20them%3C%2FP%3E%0A%3CP%3E-%20From%20the%20menu%20choose%20Remove%20columns%20drop-down%20and%20select%20Remove%20other%20columns%3C%2FP%3E%0A%3CP%3E-%20From%20the%20Remove%20rows%20drop-down%20select%20remove%20top%20rows%2C%20enter%2023%20and%20press%20OK%3C%2FP%3E%0A%3CP%3E-%20Select%20first%20column%2C%20choose%20%22Split%20column%22%2C%20%22By%20delimiter%22.%20Select%20Space%20as%20delimiter%20and%20OK.%3C%2FP%3E%0A%3CP%3E-%20Click%20%22Close%20%26amp%3B%20Load...%22%2C%20%22Close%20%26amp%3B%20Load%20to...%22%3C%2FP%3E%0A%3CP%3E-%20Select%20Table%20and%20OK.%3C%2FP%3E%0A%3CP%3ENext%20time%2C%20just%20hit%20the%20refresh%20data%20button.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Zyrandiel
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.

 

1  Raw.JPGRaw file2  Processed.JPGProcessed file

 

Regards,

Z

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies