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.
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
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.
Add sample data in column A
Add the random number of hours in column B
The addition formula in column C, will not return the correct answer
To work on hours, we need to divide the formula with 24
Hit Return Key your you can check your time is added the given number or hours
Let’s experiment with other operations
This is how you can work with hours and do all operations with the hours of the given timestamps
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.
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)
Hit enter and you’re done. you can verify it manually
Let’s experiment with other operations
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.
Further, multiply with 60.
Hit enter and you’re done. you can verify it manually to verify the result
Same as for subtraction, multiplication, and division.
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.
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
Keep the cell selected, and go to Format
Number > Custom number format
A dialog box will appear, pick the [hh]:mm:ss format
Click on apply
Now you can see the hours will exceed 24.
Download/Copy Google Sheets Workbook
- 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.
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
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.