4+ Easy Ways to Add and Subtract Time in Google Sheets

If you have worked with date and time in Google Sheets, I am sure you already know how easy it is to add dates to an already existing date in a cell.

For example, if you have the current date in a cell and you add 10 to it, it will give you the date of the day 10 days after the current date.

But can Google Sheets add time?

For example, you have the date and time in a cell, and you want to know what will be after 4 hours or after 30 minutes.

Again, this can also be done easily in Google Sheets with simple addition formulas.

In this tutorial, I will show you how to add time in Google Sheets (i.e., add hours or minutes or seconds to an already existing date and timestamp)

You can make a copy of our example time spreadsheet to follow along. If you find the example sheet helpful, you may also want to check out our paid templates where you can use the code “SSP” to save 50%.

So let’s get started!

Adding Hours to Date/Time in Google Sheets

Suppose you have the dataset as shown below where you have the timestamp (which includes the date as well as the time), and you want to add the number of hours in column B. Here’s how to calculate time in Google Sheets:

Now, unlike when adding dates, you can not simply add hours to a timestamp in Google Sheets.

You need to make sure that the unit of both columns in the same. For example, if you add the value 3 to 10:00:00, it will not give you 1 PM (i.e., the value of time after 3 hours).

This is because when you add 3 to the time, it adds 3 days to the time to which it’s added. So when you have to add some hours, you need to make sure the number actually represents hours and not days.

For example, 24 hours would be 1 day, 12 hours would be 0.5 days, as so on.

Hence, when you have the time in hours, you need to divide it by 24 to get the correct value that can be added to a timestamp that has day and time both.

Below is the formula that will give you the correct Google Sheets elapsed time when you have added hours in column B with the time in Column A:

=A2+B2/24

In the above formula, I have divided the value in column B with 24 to convert it to hours as that’s what I have in column A as well.

This formula works, but there is one thing you need to remember. When you add time such that the day changes, the resulting value would show you the time of the next day. For example, if you add 20 hours (which would be 20/24) to 10:00:00, it would give you 6:00:00, which is the time after 20 hours the next day.

Can Google Sheets Add Time in 24 Hour Format?

In case you add hours where the total is more than 24 hours, you will notice that the result will not show you the overall total number of hours that exceed 24 hours.

Rather, it would show you the time that’s in excess of the 24 hours (as shown in the image below):

In case you want to show the total number of hours, you will have to change the formatting of the cell.

Below are the steps to change the cell formatting so the total number of hours is shown:

1. Select the cells for which you want to change the cell format
2. Click the Format option in the menu
3. Hover the cursor over the Number option
4. Go to More formats and click on Custom number format
   

   

5. In the Custom number formats dialog box, enter the following format: [hh]:mm:ss
   

   

6. Click on Apply

The above steps would format numbers in the cells so that these will show the hours even when the total is more than 24 hours (as shown below in Column C)

Adding Minutes to Date/Time in Google Sheets

Just like adding time in hours in Google Sheets, you can also add minutes as well.

And again, you need to make sure that the unit of the time in the values being added needs to be the same – which is minutes.

Suppose you have a dataset, as shown below, and you want to add time in column B to the values in column A.

Below is the Google Sheets timesheet formula that will do this:

=A2+B2/(24*60)

The above formula converts the value in column B into minutes by dividing it by (24*60)

Adding Seconds to Date/Time in Google Sheets

And again, just like adding hours and minutes, you can also add seconds to the time.

And again, you need to make sure that the unit of the time in the values being added needs to be the same – which is seconds.

Suppose you have a time dataset, as shown below, and you want to add the seconds in column B to the time in column A.

Below is the formula that will do this:

=A2+B2/(24*60*60)

In the above formula, I have converted the value in Column B into seconds by dividing it by (24*60*60)

Another way to do this is by using the TIME function.

How to Sum Time in Google Sheets

You can perform the same operation using the SUM function. The most important thing to note when doing this is that your values need to be in the time format or Google Sheets duration format if you’re dealing with durations.

Let’s look at an example sheet below:

The first step would be to ensure that the format for the time is in duration. To do this we:

1. Select the time column
2. Go to Format > Number
3. Choose Duration.

Now we can use the SUM function to add up the amount of time it took to complete all the tasks.

We will use the formula:

 =SUM(B2:B6)

You will notice that the formula will automatically convert the excess seconds into minutes and minutes into hours.

Google Sheets Subtract Time

How to Find the Difference Between Two Specified Times

Just like we have added time in these examples, you can also get the Google spreadsheet time difference (just change the addition operator with the subtraction operator in the formulas).

Let’s look at our example below:

We can get the duration by subtracting the start time from the end time. Just like with adding time in Google Sheets, all we need to use is the minus sign (-).

We will use the formula:

 =B2-A2

This will give us the duration in the format hh:mm:ss. We can then copy the formula to the rest of the cells by clicking and dragging the square at the bottom right corner of the cell.

Google Sheet time formulas can be used to give you specific results. For example, if you wrap the formula above with the HOUR sheets formula, you will get the difference in hours between the two times. The same applies to Minutes and Seconds functions.

Subtracting Hours, Minutes, or Seconds from Time

The easiest way to subtract hours, minutes, or seconds from time is by using the TIME function in the equation.

Let’s look at our example sheet below:

We have used the formula:

 =A2-TIME(0,0, B2)

to get the difference in seconds in the time.

If we were working with minutes, instead, we would put the cell reference in the middle like this:

=A2-TIME(0,B2,0)

For hours we would put the cell reference first.

How to Extract Date and Time from a Full “Date-time” Record

Sometimes your data may have both the date and time in one cell in Google Sheets. In this case, it would be difficult to use add or subtract time.

You can download the Power tools in Google workspace. Here’s how:

1. Go to Extensions > Add-ons.

2. Click Get add-ons.
3. In the Add-ons window, type Power tools and click search.
4. Choose the Power Tools add-on and Click Install.

5. You may need to allow permissions. Click Continue and choose your Google account.
6. Click Done.

Using Power Tools Add-on

To make your work simpler, you can also use an add-on. The power tools add-on is one that contains a lot of functions, including a split date and time function.

Here’s how to split date and time using the power tools function:

1. Go to Extensions > Power Tools
2. Click Start.
3. Choose Split.

4. Click Split Date & Time.

5. Choose your settings, then click Split. In our case, we chose our column as a header to show you the original data and the split date and time.

This add-on will split the date and time for you and will even add headers for you. Now you can perform Google Sheets time calculations without any problems.

Summing Up

So “Can Google Sheets add time?” We’ve show the ways you can calculate time in Google Sheets (be it in hours, minutes, or seconds) including the formulas for both Google Sheets add time and subtract time.

I hope you found this tutorial useful! You can also check out how to calculate days between two dates. Alternatively, you may also want to consider taking a full Google Sheets course.

Related:

• • How to Sum a Column in Google Sheets
  • How to Sort by Date in Google Sheets (using Formula)
  • How to Calculate Age in Google Sheets