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
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.
