Take an example that you are managing a delivery service in Google Sheets, and you need to track the delivery time for packages. You have a column with the start time when the delivery begins. You want to add the delivery duration (hours, minutes, and seconds) to find the end time.
For example, if the start time is in cell A1 and the delivery duration is in hours (2), minutes (30), and seconds (45), you can use the following formula to calculate the end time:
=A2 + TIME(2, 30, 45)
This formula adds 2 hours, 30 minutes, and 45 seconds to the start time. If A2 contains 9:15:00 AM, the result will be 11:45:45 AM.
Ahead in this tutorial, we will learn to three different ways to write formula to add hours, minutes, and seconds in a time value.
Functions You Need to Know
Here you have two functions which you need to know:
- TIME FUNCTION – TIME function in Google Sheets helps you create a specific time using hours, minutes, and seconds. This is useful when you need to add a precise amount of time to a given time value. Just enter the hours, minutes, and seconds you need to add. =TIME(hour, minute, second)
- TIMEVALUE FUNCTION – TIMEVALUE function converts a text string representing a time (like “1:30 PM”) into a time value. =TIMEVALUE(time_string).
Now let’s write formulas…
Add Hours to a Time Value
In the below example, we have the time value 10:30 PM and now we need to add 1 hour to this time.
Now to add one hour to this time, you can use the below formula:
=A1+TIME(1,0,0)
When you enter this formula, and hit enter it returns the 11:30 PM which is one hour ahead from 10:30 PM. As I said, =A1+TIME(1,0,0) adds 1 hour to the time in cell A1. In this formula, A1 contains your starting time.
The TIME(1,0,0) function uses 1 hour, 0 minutes, and 0 seconds. When you add this to the time in A1, it increases the time by 1 hour.
Or, you can also use the below formula to add an hour to a time value.
=A1+1/24
The above formula adds 1 hour to the time in cell A1. Here, A1 contains your starting time. The 1/24 part represents 1 hour because there are 24 hours a day (1 hour is 1/24 of a day).
When you add this to the time in A1, it increases the time by 1 hour. And below is the third formula which you can use:
=A1 + TIMEVALUE("1:00:00") Now, this adds 1 hour to the time in cell A1. TIMEVALUE(“1:00:00”) function converts the text “1:00:00” (1 hour) into a time value. When you add this to the time in A1, it increases the time by 1 hour.
Add Minutes to a Time Value
In the same way, if you want to add 25 minutes to a time value. You need to use the same formula, but instead of adding the hours, you need to add the 25 minutes to the TIME function.
Here, A1 is your starting time, and TIME(0,25,0) represents 0 hours, 25 minutes, and 0 seconds. When you add this to the time in A1, it increases the time by 25 minutes.
And the other formula is:
=A1+25/1440
The above formula adds 25 minutes to the time in cell A1, which contains your starting time. The 25/1440 part represents 25 minutes because there are 1,440 minutes in a day (25 minutes is 25/1440 of a day).
When you add this to the time in A1, it increases the time by 25 minutes. And one more formula to add minutes to the time.
=A1 + TIMEVALUE("0:25:00") It adds 25 minutes to the time in cell A1. TIMEVALUE(“0:25:00”) converts the text “0:25:00” (25 minutes) into a time value. When you add this to the time in A1, it increases the time by 25 minutes.
Add Seconds to a Time Value
And in the same way, you can add seconds to a time value. For this, you need to use the below formula:
=A1+TIME(0,0,50)
The above formula adds 50 seconds to the time in cell A1. Here, A1 contains your starting time. The TIME(0,0,50) function represents 0 hours, 0 minutes, and 50 seconds. When you add this to the time in A1, it increases the time by 50 seconds.
And the other formula is:
=A1+50/86400
The above formula adds 50 seconds to the time in cell A1. The 50/86400 part represents 50 seconds because there are 86,400 seconds in a day (50 seconds is 50/86400 of a day). When you add this to the time in A1, it increases the time by 50 seconds.
One more formula with the use of TIMEVALUE:
=A1 + TIMEVALUE("00:00:50") The formula adds 50 seconds to the time in cell A1. TIMEVALUE(“00:00:50”) converts the text “00:00:50” (50 seconds) into a time value. When you add this to the time in A1, it increases the time by 50 seconds.
Subtract Time in Google Sheets
All the formulas, which we have learned above, you can also use it for subtracting time as well. All you need to do is to use the subtract sign instead of the add.
- Subtract Hours:
- =A1- TIME(1,0,0)
- =A1 – 1/24
- =A1 – TIMEVALUE(“1:00:00”)
- Subtract Minutes
- =A1 – TIME(0,25,0)
- =A1 – 25/1440
- =A1 – TIMEVALUE(“0:25:00”)
- Subtract Seconds
- =A1 – TIME(0,0,50)
- =A1 – 50/86400
- =A1 – TIMEVALUE(“00:00:50”)