Function Problems

How to Subtract One Hour From a Time in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to subtract exactly one hour from a time value in a specific cell and display the calculated result in a standard AM/PM time format.

Product: Excel
Device & OS: not provided
Scenario: Adjusting a schedule, converting time zones, or calculating time differences by subtracting a fixed number of hours from an existing time.
Observed behavior: The target cell must display the adjusted time in the h:mm:ss AM/PM format and properly handle instances where the subtraction might cross midnight.

Before you start

Ensure that the source cell actually contains a valid time format rather than plain text; otherwise, the formula calculation will return a #VALUE! error.

Solution 1Recommended

Use the MOD and TIME Functions

Combine the MOD and TIME functions to accurately subtract one hour while preventing negative time errors that cause Excel to display ########.

Excel calculates dates and times as continuous serial numbers, where a single day equals 1. Because of this, subtracting hours directly can sometimes result in negative values if the calculation crosses midnight. Excel cannot display negative times and will show ######## instead.

Using the MOD function with a divisor of 1 ensures the calculated time loops back around correctly within a valid 24-hour cycle, completely avoiding the negative time issue.

Select the target cell

Click on the cell where you want the newly calculated time to appear, such as cell B2.

Enter the subtraction formula

Type =MOD(A2-TIME(1,0,0),1) into the formula bar. In this formula, A2 is your original time cell, and TIME(1,0,0) represents exactly 1 hour, 0 minutes, and 0 seconds.

Format the output cell

Right-click cell B2, select 'Format Cells', choose the 'Time' category, and select the 'h:mm:ss AM/PM' format so the result matches your original data.

Fractional Alternative: Alternatively, since one hour is exactly 1/24th of a day, you can also use the formula =MOD(A2-(1/24),1) to achieve the exact same result without needing the TIME function.

WPS Spreadsheet

Subtract Time Easily in WPS Spreadsheet

You can effortlessly perform time calculations, like subtracting hours, using WPS Spreadsheet. It fully supports standard time functions like MOD and TIME, ensuring your formulas work perfectly across different platforms.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your time data.
2. Input the calculation formula: Select your destination cell and enter the formula =MOD(A2-TIME(1,0,0),1).
3. Apply time formatting: Press Ctrl+1 to open the Format Cells dialog, and select the h:mm:ss AM/PM format under the Time category.

Fully compatible with Microsoft Excel functions including MOD and TIME.Easy-to-use Format Cells dialog to quickly apply h:mm:ss AM/PM formats.Free and lightweight spreadsheet tool for efficient data analysis.

Frequently Asked Questions

Why does Excel show ##### when I subtract time?

Excel displays ##### when a time calculation results in a negative number, as the default date system cannot handle negative times. Using the MOD function as shown above prevents this by looping the time back into a positive 24-hour cycle.

How do I subtract minutes instead of hours?

To subtract minutes, you just need to adjust the TIME function parameters. For example, to subtract 30 minutes, use the formula =MOD(A2-TIME(0,30,0),1).

Can I subtract multiple hours at once?

Yes. Change the first argument in the TIME function. To subtract 5 hours, you would write the formula as =MOD(A2-TIME(5,0,0),1).

Excel & Data Problems