How to Subtract Time in Google Sheets [3 Scenarios]

How to subtract time in google sheets 19

1. Difference duration between two different times: Subtract End_Time from Start_Time > then select the resultant cell > go to the Format tab > click on Number and select Duration format.

2. Subtract the number of hours from a certain time: apply this formula “=Time-(number of hours/24)” > select the resultant cell > change the format to Time.

3. Subtract the number of minutes from a certain time: apply this formula “=Time-(number of hours/60/24)” > select the resultant cell > change the format to Time.

Welcome to another Google Sheets tutorial. In this article, we will learn how to subtract time in Google Sheets. Google Sheets is an easy-to-use spreadsheet software where you can perform anything from basic arithmetic operations to complex data analysis. Similarly, if we talk about the subtraction of time in Google Sheets then Google Sheets perfectly works in all criteria for subtraction of time in Google Sheets. Let me show you in more detail, how to subtract time in google sheets.

Purpose to Subtract Time in Google Sheets

Although subtracting time in Google Sheets is not rocket science, it is also a simple subtraction like other numbers subtraction, but you may have some complex results or errors due to changes in text format in different conditions while subtracting time in Google Sheets.

Therefore, you must learn how to subtract time in Google Sheets so that you may understand some logic used in the subtraction of time in Google Sheets.

See also  How to Pull Data from Website into Google Sheets [Web Scrapping]

How to Subtract Time in Google Sheets

The procedure of subtracting time in Google Sheets depends on different scenarios, Although the method of subtraction is as simple as other subtractions, just formula changes according to the different criteria. In this tutorial, we will discuss some of the conditions for subtracting time in Google Sheets.

  1. Difference duration between two different Times
  2. Subtract the number of hours from a certain time
  3. Subtract the number of minutes from a certain time

Subtract Time in Google Sheets – Difference duration between two different Times

Let’s suppose, if you have given two different Times and want to find the duration between these two specific times then simply subtract them and change the format for duration from the format tab as I have explained practically in the following steps.

Step 1

As you can see in the following example, we have two different types of times Time in and Time Out. Let’s subtract them, First place your cursor where you want to get the result after subtraction.

How to subtract time in google sheets 1

Step 2

To subtract time, we will use a simple formula of subtraction as highlighted in the following picture, First, we will write the Time Out cell address, subtraction sign then the Time In cell address in the syntax.

How to subtract time in google sheets 2

Step 3

As you click on the Enter key after writing syntax you will get your result instantly as can be seen in the following picture. But if you see the result is in time format with AM and PM. Let’s change its format which may confuse any user.

How to subtract time in google sheets 3

Step 4

To change its format, first select the data that you want to change for the format I have selected in the following picture.

How to subtract time in google sheets 4

Step 5

After selecting the data, go into the “Format” tab from the menu bar of Google Sheets as directed below to change the format of the specified range.

How to subtract time in google sheets 5

Step 6

When you click on the “Format” tab, a drop-down menu will drag down in which you will find a “Number” option as highlighted in the following screenshot. Click on it to change the format.

How to subtract time in google sheets 6

Step 7

When you click on the “Format” tab, it will expand to another menu as can be seen in the following picture where you will find different kinds of text formats. Select “Duration” format from these options.

How to subtract time in google sheets 7

Step 8

Now you can see the result as can be seen in the following picture, the values have been converted in time duration as we required.

How to subtract time in google sheets 8

Subtract Time in Google Sheets – Subtract several hours from a certain time

Let’s suppose you have been given a specified time and ask you to subtract any number of hours from this time then follow the following instructions. With the help of this method, you may find what the actual time would be. If you subtract XYZ’s number of hours from this time.

Step 1

Let’s suppose we have such a type of data in which we have some time values in one column and some normal numbers to subtract time in another column. Let’s perform subtraction in such a type of f data.

How to subtract time in google sheets 9

Step 2

If we apply a simple subtraction method on this type of data like time cell address minus with specified value as can be seen in the formula in the following picture.

How to subtract time in google sheets 10

Step 3

If we apply a simple subtraction formula, then you may get such a complex value as can be seen in the following picture. Now, let me tell you the exact method of applying syntax to subtract any time from any number.

How to subtract time in google sheets 11

Step 4

After the equal sign, first, insert the Time cell address, subtract the sign then open a small bracket and divide the number from which you are subtracting time by 24 if you are finding the result in hours.

How to subtract time in google sheets 12

Step 5

Now, you can see the result in the following picture, it will give you accurate duration hours as can be seen in the following picture.

How to subtract time in google sheets 13

Step 6

By applying this formula, Google Sheets will also detect the difference between AM and PM and will give you accurate results. As you can see the others result in the following picture by dragging it down.

How to subtract time in google sheets 14

Step 7

If you want to see this resultant value in time format, then you can change its format simply as above. First, select the range, then go into the “Format” tab from the menu bar of Google Sheets as directed below.

How to subtract time in google sheets 15

Step 8

When you click on the “Format” tab, a drop-down menu will open, Go into the “Number” and then select the “Time” format from the other drop-down menu as highlighted in the following screenshot.

How to subtract time in google sheets 16

Step 9

The result is in front of you, now you can find in Google what would be the time. when you subtract any number of hours from the given time as in the following example.

How to subtract time in google sheets 17

See also  How to use FREQUENCY Function in Google Sheets [Complete Guide]

Subtract Time in Google Sheets – Number of Minutes from a certain time

This method is almost the same as the above method, but in this method, we will find the subtraction of minutes from any specific time. Let’s see how we can subtract minutes from any time.

Step 1

In this method, we will subtract the number of minutes given below from the specified time in the “C” column as seen in the following example.

How to subtract time in google sheets 18

Step 2

To subtract several minutes from any time, the procedure will be the same as the subtraction of several hours as above with a minor addition in the syntax. Let me show this addition in the formula in the following example, The whole formula will remain the same just you will have to divide the number of minutes by 60 as well to subtract minutes from the given specified time as can be seen in the following picture.

How to subtract time in google sheets 19

Step 3

After writing the syntax, just press the enter key to get the result as highlighted below. With the help of this method, you will be able to subtract minutes from a certain time.

How to subtract time in google sheets 20

Frequently Asked Questions

Q: How to insert the current time and date in Google Sheets?

A: You might want to add the date and time whenever an event occurs such as opening the workbook or entering data in a specific cell. Manually inserting dates and times in google sheets can cost a lot of effort and can also cause many errors. So go through the following steps and find how to insert the current date and time in Google Sheets that will automate your sheet as well

Step 1

If you want to insert the current date and time in Google Sheets, you can easily insert it anywhere by just inserting a small formula, you will not have to go for any insert option. First place your cursor where you want to insert the date and time in Google Sheets.

How to subtract time in google sheets 21

Step 2

Once you have decided, where to insert the date and time then write the following formula “=now()” in the cell as I have written in the following example.

How to subtract time in google sheets 22

Step 3

After inserting the formula in the cell address you just need to hit the Enter key, and you will instantly get the result for the current date and time in Google Sheets as can be seen in the following example.

How to subtract time in google sheets 23

Conclusion

We learned how to subtract time in Google Sheets. I hope this tutorial is helpful to you, if so then you may check more related topics to how to subtract time in Google Sheets in Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq, I am a highly skilled technical writer working full-time for Office Demy. I am specialized in Google Workspace and Microsoft Office applications. With a background in Software Engineering, I possess a deep understanding of the intricate functionalities and features of these productivity tools. Connect me on Linkedin https://www.linkedin.com/in/shaiq-ansari/

OfficeDemy.com - Free Tutorials for Microsoft Office & Google Workspace
Logo
Enable registration in settings - general