 Last updated April 22, 2022

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.

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)

So let’s get started!

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. 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 the 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 result 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.

### Showing Time in More than 24 Hours 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 are 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 the 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 the cells so that these will show the hours even when the total is more than 24 hours (as shown below in Column C) 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 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)

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)

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

So these are the ways you can add time in Google sheets (be it in hours, minutes, or seconds).

I hope you found this tutorial useful!

Other Google Sheets tutorials you may like:

1. Ty sir

2. I think maybe this sentence:
“Hence, when you have the time in hours, you need to divide it by 12 to get the correct value that can be added to a timestamp that has day and time both.”
“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.”

• Yes, thanks for pointing that out. I’ve just fixed it 🙂

3. You wrote:
“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 12 to get the correct value that can be added to a timestamp that has day and time both.”

I think you mean divide by 24

• You are absolutely right, thanks for pointing that out! 🙂

4. Thanks. Any idea on how to do the following? I’m making quite a complicated structure with substrings. Trying to add time when it is displayed as hr and min:

Day: Driving Time:
Mon 0 hr 0 min
Tue 1 hr 45 min
Wed 1 hr 32 min
Thu 2 hr 34 min
Fri 1 hr 42 min
Total Time for Week:

• I’m sure you have figured it out but you can type text in “” into custom number formats or you could use concatenate function (with conditionals for hr vs hrs if you really wanted)

5. 6. 