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

5 Minutes

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

Time values to be subtracted in Google Sheets

2. In the adjacent cell, subtract the values

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

Using basic subtraction

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

Incorrect formatting displayed

3. Drag/Copy the formula down to other cells

Dragging formula to adjacent 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

Selecting column

5. Go to Format -> Number and Select Duration

Adjusting formatting to duration

6. Now you finally have the correct time difference 

Final difference between time values

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

Initial values

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.

Duration values to be subtracted

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

Fixing formatting

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:


5. Drag the formula to the other cells

Getting the difference

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.

Lido is a new spreadsheet built for automation. Trigger emails, slack messages, and more directly from a spreadsheet.