Calculation Issues

How to Calculate Overnight Hours in Excel Using Military Time

Amos Gikunda Oct 9, 2026 869 views

Question details

The user needs a reliable formula to accurately calculate the elapsed time or hours worked during a shift that crosses midnight using military time.

How to Calculate Overnight Hours in Excel Using Military Time

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee hours or shift durations that span across two different days, specifically crossing midnight (e.g., 18:00 to 06:45).
Observed behavior: Standard time subtraction results in negative values or errors when the end time is numerically smaller than the start time due to the midnight date rollover.

Before you start

Ensure your start and end times are entered in valid time formats (such as 18:00 and 06:45) so the spreadsheet can correctly recognize and calculate the numerical time values.

Solution 1Recommended

Use the MOD Function to Calculate Time Across Midnight

The MOD function elegantly handles negative time values, making it the most efficient solution for calculating elapsed time that crosses midnight without needing complex logic.

By dividing the difference of the two times by 1 (which represents a full 24-hour day in Excel) and returning the remainder, the MOD function automatically corrects the negative result caused by the midnight rollover.

Input time data

Enter your start time (e.g., 18:00) in cell A2 and your end time (e.g., 06:45) in cell B2.

Enter the MOD formula

Select an empty cell (e.g., C2) where you want the total hours to appear, and type the formula: =MOD(B2-A2,1)

Apply the formula

Press the Enter key to calculate the result. The initial output might look like a decimal number.

Format the result cell

Right-click cell C2, select 'Format Cells', navigate to the 'Number' tab, and choose 'Custom'. Type [h]:mm or hh:mm into the Type box and click OK.

Use the MOD Function to Calculate Time Across Midnight

Best Practice for Formatting: Use the [h]:mm custom format instead of hh:mm if you plan to sum multiple overnight shifts together. The bracketed [h] prevents the total hours from resetting to zero after reaching 24 hours.

Solution 2

Use an IF Statement to Add 24 Hours

An alternative logical method involves using an IF function to add a full day (1) to the end time only if it is smaller than the start time.

Free Spreadsheet Software

Calculate Shift Hours Easily in WPS Spreadsheet

WPS Spreadsheet is a powerful, free tool that supports all standard Excel formulas, including MOD, IF, and complex time calculations. You can easily track overnight shifts and format time data exactly as you would in Microsoft Excel without paying for a premium subscription.

1. Input military times: Open WPS Spreadsheet and type your start and end times in adjacent columns.
2. Use the formula: Type =MOD(End_Cell - Start_Cell, 1) into your total hours column and press Enter.
3. Format the time cell: Right-click the result, select 'Format Cells', choose 'Custom', and apply the [h]:mm format.

Fully compatible with Microsoft Excel formulas and time formattingBuilt-in custom number formats like [h]:mm for seamless time trackingLightweight, fast, and completely free to use for daily calculationsFamiliar interface makes migrating from Excel immediate and effortless

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel show a sequence of hashtags (######) when subtracting times?

Excel displays ###### when a time calculation results in a negative number, as standard time formatting cannot display negative times. When an overnight shift ends (e.g., 06:00) and you subtract a later start time (e.g., 22:00), the result is negative. Using the =MOD(End-Start, 1) formula corrects this.

What is the difference between hh:mm and [h]:mm formats?

The hh:mm format displays time strictly as a 24-hour clock value, meaning it resets to 00:00 after 23:59. The [h]:mm format represents elapsed or cumulative hours, allowing totals to exceed 24 hours. This is essential when calculating weekly timesheets.

How can I calculate hourly wages directly from the time calculation result?

Because spreadsheet software stores time as a fraction of a 24-hour day, you must multiply your time result by 24 to get a decimal hour value (e.g., =MOD(B2-A2,1)*24). Ensure you format this new cell as a standard 'Number' or 'Currency' before multiplying it by the hourly wage rate.

Excel & Data Problems