In this article:

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

May 8, 2024

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.

If you enjoyed this article, you might also like our article on how to subtract in Google Sheets or our article on how to subtract dates in Google Sheets. 

If you want to learn how to link emails in Google Sheets, we also suggest checking out our detailed guide. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->