Subtract a Duration from Duration in Google Sheets

Published on

Subtract a duration from duration is simple if the values are in two cells in Google Sheets. If you want either of the values or two of the values are hardcoded in the formula, then some of you may face issues.

Earlier we have learned how to group and sum a duration column using Query. Here we are just concentrating on the total duration.

I mean we have two total durations and we want to do the calculation, here subtraction, based on it.

Related: Query to Calculate Hours Worked in Week Wise in Google Sheets.

Example to Subtract a Duration from Duration in Google Sheets

Formula When the Values Are within Two Cells:

Assume you have the total duration values in two cells and the cells are D3 and D4.

If you want to subtract the D4 duration from the D3 duration, you can simply use the below formula.

=D3-D4
Example to Subtract a Duration from Duration in Google Sheets

Formula When the Values Are Hardcoded:

What about when you want to hard-code either of the values into the formula?

For example, as earlier, here also the duration 1 is in cell D3.

We want to hard-code the duration 2 in the formula and subtract the duration 2 from duration 1.

Then use the formula as below.

=D3-value("55:00:00")

You will get the subtracted value 35:50:00.

Note:

I have used the VALUE function with the second duration as per the syntax VALUE(text). Do not use the TIMEVALUE function instead of the VALUE function as it may cause a wrong answer.

Needless to say, to hard-code both the durations in the formula, use the VALUE function with both the durations as below.

=value("90:50:00")-value("55:00:00")

When you subtract a duration from duration as above in Google Sheets, you will get a numeric value (as per our example it will be 1.493055556), not a duration.

To format this numeric value to duration, go to the menu Format and apply Number > Duration.

Return 0 if the Difference is Negative

When you subtract a duration from duration in Google Sheets, sometimes the result will be negative values.

As an example, let’s use duration 1 in place of duration 2 and duration 2 in place of duration 1.

=value("55:00:00")-value("90:50:00")

This formula would return a negative duration, i.e. -35:00:00.

In this case, if you want to return 0, you can use the MAX function with the above formula as below.

=max(0,value("55:00:00")-value("90:50:00"))

How the Max is useful here?

Syntax: MAX(value1, [value2, …])

In the MAX function, we can use multiple values like value1, value2, value3

In this values (value1, value2, value3…) can be an array, reference to an array, number, or reference to a number.

Example:

=max(0,D5)

Assume cell D5 contains our duration formula result.

Since 0 is higher than a negative duration, if the result of the duration formula is negative, then the max will return 0.

This way you can subtract a duration from duration in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.