Formula 3: Subtracting time with the MOD Function TEXT formula displays the #VALUE! error, if the result is shown as a negative number.

To subtract hours, minutes, and seconds between two times: =TEXT(B2-A2, “h:mm:ss”).

To subtract hours and minutes between two times: =TEXT(B2-A2, “h:mm”).

To subtract only hours between two times: =TEXT(B2-A2, “h”).

Time Formatĭisplay hours and minutes (such as 3:20).ĭisplay hours, minutes, and seconds (such as 3:20:50). Normally, the Text Function converts any numbers into the text string within a worksheet with one of the following formats. You can subtract time between two times in Excel using the TEXT Function. Formula 2: Subtracting time with the TEXT Function Or press the key shortcut Ctrl+1, a Format Cells dialog box will appear.Ģ. In the dialog box, select the type of time format under the Time category then click OK.ģ. The decimal numbers cell has changed as hours, minutes & seconds.Ĥ. And finally, use the Fill Handle to see the results in all the expected cells.

So here we need to change the time formatting to display the spending times as hours, minutes & seconds properly.ġ. For that purpose, select the result cell of the decimal numbers ( C2 for our example) then right-click the cell and select the Format Cells in the context menu. You probably know that in the internal Excel system, excel stores times in the format of a decimal number (that’s our result). You can use a simple formula to subtract time between two-time cells.įirstly, select the result cell ( C2) and then type the formula: Formula 1: Subtracting time with a simple formula There are several ways or formulas for subtracting time in Excel that we are going to discuss below. To determine the spending time, we frequently need to calculate the difference time between cells. Combine functions so that Hash (#) is not displayed.įormula for subtracting time in Excel between two times to get the time difference.How to change Excel date system to 1904 date system.Results Showing Hash (#) Instead of Date/Time (Reasons + Fix).Formula 8: Display subtracting time as “XX days, XX hours, XX minutes, and XX seconds.”.Formula 7: Subtracting time with the IF Function.Subtracting time from the start to now to get the Elapsed Time Subtracting time into one unit (hours, minutes, or seconds) ignoring others Subtracting time in hours, minutes, or seconds Formula 3: Subtracting time with the MOD Function.Formula 2: Subtracting time with the TEXT Function.Formula 1: Subtracting time with a simple formula.Formula for subtracting time in Excel between two times to get the time difference.

In this guide, you will learn the following Toggle

Excel formula for subtracting