VBA & Macro Problems

How to Convert Military Time to Standard Time in Excel

Elise Williams Oct 19, 2026 869 views

Question details

The user needs to enter 4-digit military times (like 1300) without colons and have them automatically convert into standard 12-hour AM/PM Excel time formats.

How to Enter Military Time in Excel and Display It as Standard Time

Product: Excel
Device & OS: not provided
Scenario: Entering large amounts of fast 4-digit time data and needing them recognized as actual calculation-ready Excel time values instead of basic numbers.
Observed behavior: Standard custom formatting (like 0":"00 AM/PM) fails to calculate a valid Excel time value, simply masking the number and causing afternoon values like 1300 to display incorrectly as "1300 PM".

Before you start

Ensure your Excel workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that macros are permitted in your Trust Center settings so the VBA code functions correctly.

Solution 1Recommended

Use a Worksheet-Change VBA Macro for Automatic Conversion

Implement a VBA macro to intercept the four-digit number when you type it and automatically calculate the correct Excel time value based on the hours and minutes entered.

Because custom formatting alone only alters the appearance of digits without changing the underlying mathematical value, it cannot turn whole numbers like 1300 into a true Excel time. Using a VBA Worksheet-Change event ensures the data is parsed and converted accurately for future calculations.

Set up the target cell format

Select the range where you plan to enter military times (e.g., A1:A100). Right-click, select Format Cells, navigate to Custom, and type "h:mm AM/PM".

Open the Visual Basic Editor

Right-click the sheet tab at the bottom of your Excel window where you want the conversion to occur and select "View Code".

Paste the conversion macro

Copy the required VBA code that divides the entered string into hours and minutes, and paste it directly into the empty code module window.

Adjust the target range

In the code, locate the text Range("A1:A100") and modify it to exactly match the cell range where you intend to type your times.

Test the input

Return to your worksheet and type a 4-digit time like 1300 or 0854 into the designated range. It should instantly display as 1:00 PM or 8:54 AM.

Use a Worksheet-Change VBA Macro for Automatic Conversion

VBA Code Snippet: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A100")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub A = Target.Value If A >= 1000 Then C = Left(A, 2) D = Right(A, 2) Target.Value = C / 24 + D / 24 / 60 End If If A < 1000 And A > 1 Then C = Left(A, 1) D = Right(A, 2) Target.Value = C / 24 + D / 24 / 60 End If End Sub

Efficient Spreadsheet Automation

Automate Data Entry with WPS Spreadsheet

WPS Office Spreadsheet provides excellent support for VBA macros. You can easily execute worksheet-change events and instantly convert numeric inputs into accurate time formats, accelerating your data entry workflows.

1. Launch the VBA Editor: Open your workbook in WPS Spreadsheet, navigate to the Developer tab, and click "VB Editor".
2. Apply code to the worksheet: In the Project Explorer panel, double-click the specific worksheet you are using, and paste the military-time conversion macro.
3. Save and automate: Save your file as a Macro-Enabled Workbook and start typing in your specific range to enjoy instant time conversions.

Full support for advanced VBA macro executionSeamless compatibility with Microsoft Excel .xlsm and .xlsx formatsIntuitive and easy-to-use custom cell formatting optionsFree, lightweight, and fast to launch without heavy resource consumption

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't custom cell formatting work for military time?

Custom formatting only alters how a number visually displays. If you type 1300, Excel reads it as the integer one thousand three hundred, not the time value for 1:00 PM. This mismatch causes errors in any downstream time-based calculations.

Do I need to save my file differently after adding the macro?

Yes. If you insert VBA code, you must "Save As" and select Excel Macro-Enabled Workbook (.xlsm). If you save it as a standard .xlsx file, the macro will be stripped out and won't work the next time you open the document.

Can I apply this macro to an entire column instead of a specific block?

Yes, you can modify the VBA code to target full columns. Simply change Range("A1:A100") to Range("A:A") in the script, and the conversion logic will apply to any cell within column A.

Excel & Data Problems