Calculation Issues

How to Calculate and Show Excel Duration Without AM/PM

Nimra Malik Sep 28, 2026 870 views

Question details

The user needs to accurately calculate the duration of overnight shifts and format the result as total hours and minutes (e.g., 9:30) without displaying AM or PM.

How to Calculate and Show Excel Duration Without AM/PM

Product: Excel
Device & OS: not provided
Scenario: Calculating employee work hours or timesheets for shifts that span across midnight.
Observed behavior: The calculated duration displays unexpected results, such as 21:30 instead of 9:30, often due to incorrect input data or formatting that misinterprets the duration as a time of day.

Before you start

Verify that your source start and end times are entered correctly as true time values (e.g., 20:00 or 8:00 PM), and confirm that the intended shift definition matches the data entered.

Solution 1Recommended

Verify Input Data and Calculate Overnight Duration

Use this solution to correct unexpected calculation results caused by erroneous source times or improper formulas for overnight shifts.

Unexpected shift durations, such as getting 21:30 instead of 9:30, are usually caused by incorrect input data from a colleague or user error. For example, a shift from 20:00 to 17:30 the next day mathematically equals 21.5 hours. If the intended shift was 21:00 to 05:30 (8.5 hours), the source data must be corrected first.

Once the data is correct, you must use a formula that can handle overnight shifts where the end time appears smaller than the start time.

Audit your start and end times

Double-check the source data cells. Ensure that a PM time was not accidentally entered as an AM time, and that the intended shift times are accurately typed.

Apply the MOD formula

Select the cell where you want the duration to appear and enter the formula: =MOD(End_Time - Start_Time, 1). For example, if your start time is in A2 and end time is in B2, enter =MOD(B2-A2, 1).

Press Enter to calculate

Hit Enter to apply the formula. This function correctly calculates the time difference regardless of whether the shift crosses midnight.

Verify Input Data and Calculate Overnight Duration

Why use the MOD function?: The MOD function with a divisor of 1 seamlessly handles negative time calculations that normally result in ####### errors when a shift goes past midnight.

Solution 2

Apply a Custom Duration Format Without AM/PM

Use this method to format the calculated time difference so it displays as a pure duration of hours and minutes, hiding any AM or PM designations.

Calculate Time Easily with WPS Office

Manage Work Shifts and Timesheets in WPS Spreadsheet

WPS Spreadsheet offers powerful built-in formulas and custom formatting options, making it incredibly simple to calculate overnight shift durations and hide AM/PM markers.

1. Open your timesheet: Launch WPS Spreadsheet and open your document containing the shift start and end times.
2. Calculate the difference: Type the formula =MOD(B2-A2, 1) in your duration column to accurately calculate hours worked.
3. Access cell formatting: Select the calculated cells and press Ctrl+1 to open the Format Cells window.
4. Apply custom duration: Choose Custom, type [h]:mm in the format box, and click OK to display hours purely as duration.

Perfectly process overnight shift calculations and time differences.100% compatible with Microsoft Excel formulas and custom formats like [h]:mm.Lightweight, fast, and free alternative for managing complex timesheets.User-friendly interface with intuitive cell formatting tools.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel time calculation show #####?

This happens when an end time is mathematically smaller than the start time (like 02:00 AM minus 10:00 PM), resulting in a negative number. Excel's default date system cannot display negative times. Use the =MOD(End-Start, 1) formula to fix this.

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

The 'h:mm' format is tied to a 24-hour clock cycle. If a duration is 25 hours, it will roll over and display as 1:00. The '[h]:mm' format accumulates total hours without resetting, so it will accurately display 25:00.

Can I sum the total hours for the week after using the MOD formula?

Yes. Once you have calculated daily durations, you can use the standard =SUM() function to add them up. Ensure the total cell is formatted with '[h]:mm' so that weekly totals exceeding 24 hours display correctly.

Excel & Data Problems