Formula Errors

How to Calculate Military Time Without a Colon in Excel

Steve K Oct 7, 2026 870 views

Question details

The user needs an Excel formula to calculate total elapsed hours using four-digit military time inputs (without colons) while factoring in a 30-minute lunch break.

How to Calculate Military Time Without a Colon in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating employee work hours or shifts based on numeric military time inputs (e.g., 0800, 1700), requiring conversion to time formats and break deductions.
Observed behavior: Without a colon, Excel treats military time as standard integers, making it difficult to calculate elapsed time accurately, especially across overnight shifts.

Before you start

Ensure your start and end times are entered as raw numerical values (like 800 or 1700) and that the output cell is formatted as a Number to display the total hours correctly.

Solution 1Recommended

Use the TEXT Function to Calculate Elapsed Time

Use a specialized formula combining the TEXT function to insert a colon dynamically, handle overnight shifts, and subtract a 30-minute break.

By converting the raw numbers into recognizable time formats using the TEXT function, Excel can properly calculate the difference. The formula also includes logic to prevent negative time errors during overnight shifts and deducts the correct fractional amount for a 30-minute break.

Select the target cell

Click on the cell where you want the calculated total hours to appear.

Enter the formula

Type the formula =(TEXT(C3,"00\:00")-TEXT(B3,"00\:00")+(B3>C3)-0.5/24)*24. In this formula, B3 represents the start time and C3 represents the end time.

Format the output cell

Press Enter to apply the formula. Right-click the cell, select Format Cells, and choose Number or General to ensure the result displays as standard decimal hours.

Use the TEXT Function to Calculate Elapsed Time

Understanding the Formula: The (B3>C3) segment correctly adds 24 hours if the end time is numerically smaller than the start time (overnight shifts). The -0.5/24 segment deducts exactly 30 minutes from the total day.

Efficient Spreadsheet Management

Calculate Work Hours Seamlessly with WPS Spreadsheet

WPS Office provides a highly compatible spreadsheet environment that perfectly supports advanced functions like TEXT and time calculations. Easily process payroll, manage employee timesheets, and handle complex formulas with zero friction.

1. Open your timesheet: Launch WPS Spreadsheet and open your existing timesheet or start a new blank workbook.
2. Enter your raw time data: Input your four-digit military start and end times into separate columns without typing colons.
3. Apply the calculation formula: Paste the provided TEXT formula into the total hours column and use the fill handle to drag it down for all employees.

100% compatibility with Microsoft Excel formulas and time formattingLightweight and extremely fast for processing large employee timesheetsBuilt-in templates for daily tracking, scheduling, and payrollCompletely free to use for everyday data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

How do I change the 30-minute lunch break to a 1-hour break in the formula?

To subtract a full hour instead of 30 minutes, replace the -0.5/24 portion of the formula with -1/24.

Why does my start time drop the leading zero (e.g., 0800 becomes 800)?

When cells are formatted as numbers, standard spreadsheet software automatically removes leading zeros. The TEXT(..., "00\:00") component in this formula is specifically designed to handle this by padding the number internally to ensure the correct time format is generated.

What is the formula to calculate time without subtracting any lunch break?

If you do not need to deduct a lunch break, simply remove the break deduction portion from the equation. The adjusted formula is: =(TEXT(C3,"00\:00")-TEXT(B3,"00\:00")+(B3>C3))*24.

Excel & Data Problems