How to Subtract Time in Google Sheets (Best Method!)

Subtracting time in Google sheets might sound simple but sometimes formatting can complicate things and leave you with an incorrect answer. In this article, we will help you get the time difference in the easiest way possible. 

Subtracting Time in Google Sheets to Get Duration 

1. Prepare the time values you want to subtract

‍

2. In the adjacent cell, subtract the values

Do this by using =B2-A2 in cell C2 and pressing enter

Notice how the answer indicates 1:00:00 AM instead of showing one hour.

3. Drag/Copy the formula down to other cells

It’s important to check that there are no errors after you subtract time values. Row 8 indicates 12:35 instead of a 35 minute difference. We’ll fix this problem using formatting. 

‍

4. Highlight Results so We can Change the Formatting

5. Go to Format -> Number and Select Duration

6. Now you finally have the correct time difference 

Subtracting a Duration from A Given Time in Google Sheets

‍

Say you wanted to subtract a certain amount of hours or minutes from a given time, the steps below will show you how to achieve this.

1. Prepare your initial time values in the first column

‍

Here we will use time in 00:00:00 AM/PM formats

‍

You can notice that Google will treat your values as time values by default and will affix AM or PM at the end.

2. Input the time values you want to subtract in the second column

‍

Here we will use HH:MM:SS formatting to indicate how much hours, minutes and seconds we will be subtracting from our values. If you want to subtract one hour, 20 minutes and 32 seconds from our time value, type 1:20:32.

‍

3. Change the Formatting to Duration by Going to Format -> Number and Selecting Duration

This will ensure that our values are treated as durations and not daytime values. Doing this will also allow you to subtract more than 24 hours without messing up the differences.

‍

4. Subtract the two values in the third column using the formula:

=A2-B2

5. Drag the formula to the other cells

We see that number formatting is key in subtracting time in Google Sheets. Now you can use this to achieve your needs for event planning or time data analysis. Click here to learn how to add time values.

‍