How to Add Time in Google Sheets (Best Practice)

how to Add Time in Google Sheets 23

In this article, we will learn how to add time in google sheets.

If you have worked with dates before, you must know how to add or subtract the dates, the benefit of adding the dates or the time is to calculate another time or data which is very useful you will see in this article. Just like dates, time can also be added, subtracted, multiplied, and even divided with other integers so that we can have important calculations within the time format. To add the time we don’t have complex functions or formulas to use, we simply need to understand the conceptualization behind the time in google sheets.

As we can do all the operations on dates very easily, because the dates do not have minutes or seconds, if we add 1 to 7/dec/2021 it will make it 8/dec/2021. But when we do the same with the time, we have to tell google sheets about minutes and seconds along with hours to get accurate results.

See also  How to Make Categories in Google Sheets [Guide 2023]

Use Cases of Adding Time in Google Sheets

We often deal with timestamps in google sheets, we have to manage different kinds of datatypes like integers, strings, objects, formulas, links, XML, dates, and time. So we must know how to manipulate them and use them along with other data types, when we have timestamps coming from a source, for example, I have an online store and I am using some script to extract the order time on my store and the script automatically extract that timestamp into google sheets.

Now what if I have to alter the data and have to make small changes in the time extracted by the script. I will have to do manually to all the timestamps it will take a lot of time because the entries can be in hundreds or even in thousands and we don’t know if they can also be in millions, so we must know how to add time in google sheets.

  • To add time with integer types.
  • To make small changes in the time extracted by some kind of formula of the script.
  • To see the before and after the time of the exact time
See also  How to Set Print Area in Google Sheets [Complete Guide]

How to Add Time in Google Sheets

Here we will see what are the steps or methods required to learn how to add time in google sheets. We will understand the time theory in the google sheets and then we will move forward to the application and we will learn it practically.

Let’s start with the basics, and see how to add time in google sheets.

How to Add Time in Google Sheets – Understanding Time Format

Times do not work in the same way as dates. Adding an integer to a time let’s say 10:00 AM will be 10:00 AM, after adding a number let’s say 3, but when I add the same number 3 to a date, let’s say 07-12-2021, it will become 10-12-2021.

So, If 1 (as a day) is equal to 24 hours, then one hour is equal to 1 / 24, one minute is equal to 1 / (24*60), and similarly, one second is equal to 1 / (24*60*60)

Having said that I hope you guys have got the logic, this is the logic on which you can do anything with dates in google sheets

Let’s move on to see practical examples for various use cases of time in google sheets

How to Add Time in Google Sheets – Adding Hours

I have a sample data set in which I have some timestamps in column A, and in column B I have some random numbers written as hours (in number format), I will add the timestamps with the numeric numbers and in column C you will see that the time has taken the given number of hours and now time is changed.

Step 1

Add sample data in column A

how to Add Time in Google Sheets 1

Step 2

Add the random number of hours in column B

how to Add Time in Google Sheets 2

Step 3

The addition formula in column C, will not return the correct answer

how to Add Time in Google Sheets 3

how to Add Time in Google Sheets 4

Step 4

To work on hours, we need to divide the formula with 24

how to Add Time in Google Sheets 5

Step 5

Hit Return Key your you can check your time is added the given number or hours

how to Add Time in Google Sheets 6 

Step 6

Let’s experiment with other operations

Subtraction

how to Add Time in Google Sheets 7

how to Add Time in Google Sheets 8

Multiplication

how to Add Time in Google Sheets 9

how to Add Time in Google Sheets 10

Division

how to Add Time in Google Sheets 11

how to Add Time in Google Sheets 12

This is how you can work with hours and do all operations with the hours of the given timestamps

See also  How to use PERCENTILE Function in Google Sheets [Beginner's Guide]

How to Add Time in Google Sheets – Adding Minutes

To add minutes we all have the same procedure just a change in the formula is to be made to make it work on minutes rather than hours.

Step 1

Further multiply the hours with minutes, as we divided by 24 to make hours, now further will multiply it by 60 to make minutes (as we have 24 hours in a day, then 60 minutes in an hour)

how to Add Time in Google Sheets 13

Step 2

Hit enter and you’re done. you can verify it manually

how to Add Time in Google Sheets 14

Step 3

Let’s experiment with other operations

Subtraction

how to Add Time in Google Sheets 15

how to Add Time in Google Sheets 16

Multiplication

how to Add Time in Google Sheets 17

how to Add Time in Google Sheets 18

Division

how to Add Time in Google Sheets 19

how to Add Time in Google Sheets 20

See also  How to Do IF THEN in Google Sheets [IF ELSE Condition]

How to Add Time in Google Sheets – Adding Seconds

To add seconds we all have the same procedure just another change in the formula is to be made to make it work on seconds rather than hours or minutes.

We need to further multiply by 60, as we have 60 seconds in each minute. I hope you have understood the pattern, by default it captures a day if you add a number to a time (that’s why it remains unchanged), it shows you the same time after 3 days that’s why the time looks like it’s not changed.

Step 1

Further, multiply with 60.

how to Add Time in Google Sheets 21

Step 2

Hit enter and you’re done. you can verify it manually to verify the result

how to Add Time in Google Sheets 22

how to Add Time in Google Sheets 23

Same as for subtraction, multiplication, and division.

I hope you have got an understanding of the patterns, how google sheets capture the time and how it evaluates the operations on days, hours, minutes, and seconds, there is no microsecond in google sheets as we have it in programming languages like JavaScript.

One more thing I would like to cover in the context of this article, you may have noticed that the time is rounding to the next day as it has more hours than 24.

See also  How to use Named Ranges in Google Sheets (User Guide)

How to Add Time in Google Sheets – The Format to show more than 24 hours

We can control it by changing the formatting for time. Let’s show how it’s done

Step 1

Keep the cell selected, and go to Format

how to Add Time in Google Sheets 24

Step 2

Number > Custom number format

how to Add Time in Google Sheets 25

Step 3

A dialog box will appear, pick the [hh]:mm:ss format

how to Add Time in Google Sheets 26

Step 4

Click on apply

how to Add Time in Google Sheets 27

Now you can see the hours will exceed 24.

Download/Copy Google Sheets Workbook

Notes

  • Remember that timestamps do not work as dates, you have to specify hours, minutes, and seconds to get accurate results.
  • If you add 20 hours as an integer, in the timestamp of 20:00 (24-hours format), it will be rounded to the other day and the time will become 06:00 of the next day because you are working as hours and not day.
  • You can change the format if you want to get the result as per hours, so that hours will not round to the next day and they will be exceeded more than 24 hours, in this situation the above example will give the result of 40 (hours)
  • This can be done by selecting the cell(s). Go to Format > Number > Custom number format, and then write the format [hh]:mm:ss, and apply it.
  • Similarly for seconds and minutes, if you don’t want them to round in hours and minutes respectively, you can use the above format to exceed them from hours and minutes.
  • Dates can be directly added with a number and if you add 10 in 7-12-2021, it will become 17-12-2021. You don’t need to specify day, month, year, etc. in this case.

See also  How to Make a Beautiful Chart in Google Sheets (User Guide)

FAQ

How to subtract with time in google sheets

You can simply subtract the time in google sheets by seeing any above example, I will quickly define hare, and you can reference the above method to understand it in detail. firstly, you have your timestamp written let’s say (10:00 AM) now you want to subtract, so you subtract this by 4, so you should get the time as (06:00 AM), it simply is done, as we are working on hours so we will have to divide by 24, a simple formula divided by 24 to get the desired result.

Here is the formula for the above case

=A1-(B1/24)

how to Add Time in Google Sheets 28

how to Add Time in Google Sheets 29

See also  How to Sort Alphabetically in Google Sheets (A-Z or Z-A)

Conclusion

Wrapping up how to add time in google sheets, we have learned absolute details about the time in google sheets. We started with the basic theory of time in google sheets and tried to understand how google sheets read the time and how to perform a calculation on time (hours, minutes, and seconds). We saw a practical example of adding hours, then we saw practical examples of adding minutes, and then we saw practical examples of seconds.

In all three cases, we also saw how we can perform other operations on the timestamps such as subtraction, multiplication, and division. Note that: Multiplication and divisions are not used in this manner, normally we use addition and subtraction methods to go behind and ahead of a given timestamp. In the end, we also tried to learn a technique of time formatting [hh]:mm:ss by which we can allow the hours to exceed the 24 hours and appear in the actual number for example if I add 23:00 with 2, normally it will show me 1:00. But using the above format you can make it 25, we need this to happen when we are working on hours purely and we want figures in hours regardless of day change. For example, when calculating the working hours of employees. That’s all from How to add time in google sheets. Will see you soon. Keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general