What Is Subtract Time in Google Sheets?
Using Google Sheets, you can perform several phenomenal functions on the date and time. One of the most important applications is to calculate the difference between times, adding up of times, and so on. Many business organizations and financial analysts require time control and understanding of time-related calculations. Google Sheets can help you calculate the time difference in many ways. For instance, look at the example below.
By default, Google Sheets formats the time as “hh:mm”.” In the example below, we find the difference between the two times as the End time-start time. If you need results only in the form of hh, mm, or ss, you must apply a custom format.

Key Takeaways
- You can subtract time in Google Sheets using a cell reference in a very basic way. To find the time difference, subtract one time from another using the basic minus – sign.(e.g., =B1 – A1).
- You can also use the MINUS() function to subtract one value from another.
- You can use the TIME function to subtract fixed hours, minutes, or seconds using the TIME function (e.g., =A1-TIME(5, 30, 0)).
- Similar to subtracting time difference in Google Sheets, you can also add time in Google Sheets as =A1+TIME(5,20,0)
How to Subtract Time in Google Sheets?
To subtract time in Google Sheets, you do not have to use complicated formulas. Just use the standard “-” (minus) operator.
Let us look at the step-by-step process of subtracting time in Google Sheets.
Step 1: In a Google Spreadsheet, enter some start times in Column A and end times in Column B. In both cases, we have chosen the ‘Duration’ option.

Step 2: Now, in Column C, select Format – Number – Time.
So, our result should be in the format ‘Duration.’

Step 3: In the C2 cell, let us enter the following formula and press Enter.
=A2-B2
The resulting time will appear in the C2 cell.

Step 4: Now, drag the formula to C3 as well. Thus, you get the difference in time in your required format.

Find the Difference Between Two Times as hours, minutes or seconds
To find the difference between two times, we use the following method: In the same sheet as above, you can apply the following formulas to get the hours, minutes, and seconds.

Thus, we can extract the difference in hour, minute, and second using =HOUR(cell address), =MINUTE(cell address), and =SECOND(cell address), respectively.
These functions can only work within 24 hours, 60 minutes and 60 seconds. Beyond this, the formulas will return errors.
Another way to subtract time is to subtract 45 minutes from the time in cell A3.
The formula is as follows:
- Difference = =starttime-TIME(0,45,0)
Let us apply this in cell A4 and check the result.

You can see that 45 minutes have been subtracted.

Examples
Now, let us discuss the different ways of calculating time in Google Sheets. The examples provide some interesting ways of finding time differences.
Example #1 – Calculate Employee Work Hours
Let’s calculate the work hours of three employees in Google Sheets. We’ll also calculate the maximum and minimum hours they worked in a particular week.
Look at the employee details entered in the spreadsheet below.

Step 1: Now, to get the working time of both employees across the week, let us apply the following formula to cell D3.
=C3-B3

Step 2: Press Enter.

Step 3: Now, apply a similar formula to both employees throughout the week.

Thus, you can get an idea of the hours worked by the employee. Now, to find the
the shortest and longest hours worked by an employee, use the formulas =Min(hours worked) for the minimum hours worked, and =Max(Hours Worked) for the maximum hours worked.
In the example, we add =MAX(D3:D16) and =MIN(D3:D16) in cells E17 and E18, respectively.

Example #2 – Track Project Time
We can use Google Sheets to subtract time to track project time. Suppose a client wants to complete a project in 3 months. The daily tasks of execution, monitoring, and control should be tracked. Here, it is very useful to subtract time. For instance, listed below is the average time taken by each employee when their task is completed. You can subtract the time easily to find how much time is spent on each task to track project time.

Step 1: From the data above, let’s find the average time taken by the employees. To do so, apply the formula =B2 – A5 in cell C2.
Here, we have to enter the time the organization begins work in cell A5, which is 9:30:00 AM.

Step 2: Now, enter the formula to C3 to determine the time taken for testing. We have formatted Column C with type the Duration.
It comes under Format – Number -Duration.
Enter the formula =B3-B2 in C3.

Step 3: Now, to find the timing for the “Monitoring process,” drag the formula to cell B4.

You get an idea of the total time taken for the tasks.
Example #3 – Alternative Subtraction Formula (MINUS function)
To subtract time on Google Sheets, you can also use the MINUS operator.
Let us look at the start and end times of a student trying to keep track of his study hours. He logs the timings to find if his performance is directly related to his score.

Step 1: Now, enter the following formula in cell D2.
=MINUS(C2,B2)

Step 2: Press Enter. Now, drag the formula till D8.

Step 3: Now, to find the correlation between the score and time durations,

Step 4: Now, in the chart editor pane on the right, choose the scatter chart.

You can observe that his score is lesser for the lower duration of study hours compared to the higher, which proves that he must study hard for at least 4 hours. to better his score.
Example #4 – Convert Time to Fraction using TIMEVALUE4
TIMEVALUE returns the fraction of a 24-hour day the time represents. Let’s use the previous example’s start and end times to find their time values.
Step 1: In the table shown below with the start and end times, let’s first apply the time value for the start time. Then, apply the following formula to cell C2.
=TIMEVALUE(A2)

Step 2: Drag the formula to cell C8.

Step 3: To find the fractional value of the end time, apply the formula =TIMEVALUE(B2). Drag the formula till D8.

Now, let us subtract these two values to get the fractional time difference in Column E.

Important Things To Note
- If you want to extract hours from your result, enter =HOUR(D2) in a cell. To extract minutes and seconds, use =MINUTE(D2) and =SECOND(D2) in any cell.
- Similarly, you can add hours to any time as follows: =Start time + TIME(N hours, 0, 0).
- Ensure all time cells are formatted as Time before applying the minus sign or using MINUS.
- Use the TIMEVALUE function to turn a valid time in a text format into a time stored as a fraction.
- You should always format the cells containing times in a specific format using Format -> Number -> Time or Duration. After this, you can then use the usual + or – operators to add and subtract the time.
Frequently Asked Questions (FAQs)
Google Sheets has some very useful functions that help you find the Google sheets time difference in minutes, hours, and seconds. However, these functions are available only for 24 hours and not days. =HOUR(B1-A1) – returns only the difference in the number of hours between the two times.
=MINUTE(B1-A1) – returns the minutes only
=SECOND(B1-A1) – returns the seconds’ difference between two times.
If you also need to calculate the negative time difference and subtract time in Google sheets, you must use the IF condition. You can use the ABS function or check if the first time is greater than the second using IF as shown below.
=IF(A1 > B1, A1 – B1, B1 – A1)
First and foremost, we should ensure that the time entries are in the same format. You can change the format of all the dates which you will use to the same format. Else, you can convert text to time using the TIMEVALUE function, as shown in Example 4 above.
=TIMEVALUE(A1).
Download Template
This article must be helpful to understand the Subtract Time in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Subtract Time in Google Sheets. We learn syntax & how to use Subtract Time in Google Sheets , examples, working template. You can learn more from the following articles.
Leave a Reply