Subtract a Duration from Duration in Google Sheets

Are you struggling to subtract a duration from another duration in Google Sheets? Look no further! I’ve got you covered with easy-to-follow steps that will have you subtracting durations like a pro in no time.

Example to Subtract a Duration from Duration in Google Sheets

Let’s start with a simple example to illustrate how to subtract durations when the values are in two separate cells.

Assume you have the total duration values in cells D3 and D4. To subtract the duration in D4 from the duration in D3, simply use the following formula:

=D3-D4

But what if you want to hard-code one of the values into the formula? For instance, if the duration 1 is in cell D3 and you want to subtract a specific value (let’s say 55:00:00) from it, here’s what you need to do:

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

This formula will give you the subtracted value, such as 35:50:00.

Note: Make sure to use the VALUE function with the second duration in the formula. Avoid using the TIMEVALUE function, as it may yield incorrect results.

If you want to hard-code both durations in the formula, here’s how you can do it:

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

However, keep in mind that when you subtract a duration from another duration in Google Sheets, the result will be a numeric value (e.g., 1.493055556) rather than a duration.

To format this numeric value as a duration, simply go to the menu Format and select Number > Duration.

Return 0 if the Difference is Negative

Sometimes, when you subtract a duration from another duration in Google Sheets, the result may be a negative value. In such cases, you might want to return 0 instead.

Let’s say you use duration 1 in place of duration 2 and duration 2 in place of duration 1. The formula =value("55:00:00")-value("90:50:00") would return a negative duration, like -35:00:00.

To ensure that you get a 0 instead of a negative value, you can use the MAX function as follows:

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

In the MAX function, you can specify multiple values, such as value1, value2, value3, and so on. If the result of the duration formula is negative, the MAX function will return 0 because 0 is higher than a negative duration.

With these handy tips, you’ll be able to subtract durations in Google Sheets effortlessly. Say goodbye to hassle and hello to accurate calculations!

For more valuable insights on Google Sheets and other amazing tricks, don’t forget to visit Crawlan.com.

Now go ahead and impress your friends and colleagues with your newfound skills in subtracting durations in Google Sheets!