How to subtract time in Google Sheets

By SpreadCheaters
Page last updated: 21/04/2023 | Next review date: 21/04/2025
close

Author bio


SpreadCheaters
View More north_east

Google Sheets is a cloud-based spreadsheet software developed by Google. It is part of the Google Drive suite of productivity tools and is available for free to anyone with a Google account. Google Sheets allows users to create and edit spreadsheets online, share them with others, and collaborate in real-time with multiple users. Subtracting time in Google Sheets is a common operation that is useful for calculating time differences or durations between two timestamps.

In this tutorial we will learn how to subtract time in Google Sheets. To subtract time in Google Sheets, you can use a combination of the “-” operator and the TEXT function or you can simply subtract the time and change the format of the cell to duration.

In Method 1 we have a data set containing End time and Start time in the Time Format.We will subtract the start time from the end time in order to calculate the time difference between both.In the Method 2 we have a data set containing End time and Start time in Date and Time format.

Method 1 : Subtracting Time using Text function

Step 1 – Select a Blank Cell

  • Select a blank targeted cell where you want to subtract the time.

Step 2 – Place an Equals Sign

  • Place an Equals (=)  sign in the blank cell.

Step 3 – Use the TEXT function

  • The syntax for TEXT function to subtract time will be 

                             TEXT(B2-A2,”h:m”)

  • The first argument is the numerical value, the format of which is to be changed i.e. the difference B2-A2.
  • Where B2 is the End Time and A2 is the Starting time.
  • The second argument is the Format which you want to apply i.e. “h:m” where h represents hours and m represents minutes.

Step 4 – Press the Enter Key

  • Press the Enter key to get the difference.

Step 5 – Apply the Function on Each Time Interval

  • Use the “Handle Select” and “Drag and Drop” method to apply the function on each time interval.

Method 2 : Subtracting Time between two Dates

Step 1 – Select a Blank Cell

  • Select a blank targeted cell where you want to subtract the time.

Step 2 – Place an Equals Sign

  • Place an Equals (=)  sign in the blank cell.

Step 3 – Use the “-” operator to Subtract Time

  • Subtract Start time from End time using the “-” operator.

Step 4 – Press the Enter Key

  • Press the Enter key to get the difference.

Step 5 – Select the Cell and Change its Format

  • Select the cell containing the time difference.
  • Go to the Format Tab in the menu bar, a drop-down menu will appear.
  • Click on the Number option and select Duration as the format.
  • The difference will now be converted in hours.

Step 6 – Calculate the Time Difference for Each Time Interval

  • Use the “Handle Select” and “Drag and Drop” method to Calculate the Time Difference for Each Time Interval.