What Is Add Time In Google Sheets?
The Add Time in Google Sheets helps users to calculate the sum of time values such as, the total time taken to perform given tasks such as completing a project, assignments or time taken to travel, etc. The Google Sheets Add time feature can be used to add minutes, seconds and hours to a give time value. However, we must have a minimum of two values, namely, the start and the end time.
For example, we have the time values given below. We will add them and find the total using the SUM function that is always used to calculate the total of numeric values.
Select cell C2, enter the formula =SUM(A2,B2) and press “Enter”, as shown below.
The output is shown above. The Time values are added and the total time is returned, taking the AM and PM into consideration.
Key Takeaways
- The Add Time in Google Sheets helps us find the total time using the SUM function or the arithmetic operator “+”. Also, we can add the time values, and then format the result as required.
- We can also calculate the remaining time for completion of tasks, using the subtraction operator “-”, i.e., calculate the time difference. We must remember, whenever we calculate the difference of time values, we must always subtract the lower value from the higher value.
- While calculating the duration in terms of hours, minutes or seconds, ensure to format the result cells initially or after we calculate the results. It will help us get precise time values or duration.
- The time format applied to a cell would only change its display presentation without changing the cell value.
- If we use the arithmetic operators to Add and Subtract time values and we insert the time values directly, not as cell reference, always ensure to enter the direct values within double-quotes.
Syntax
We can add any numeric values in MS Excel or Google Sheets using the SUM function. Therefore, we will use the same to Add Time in Google Sheets.
The syntax of the Google Sheets SUM function is,
The arguments of the Google Sheets SUM function are,
value1, [value2, …] – It is the numeric values in a dataset. In this article, we will use the Time values in the valid time format.
How To Add Time In Google Sheets?
We can Add Time in Sheets in a couple of ways, namely,
- Using the SUM function – In an empty cell, enter the SUM formula and select the time values or cell range to get the total.
- Using the ‘+’ operator à In an empty cell, enter the “=” sign, the first value (either enter the value or select the cell reference), insert the “+” sign and then the second value. Repeat the procedure till all the values are entered.
The final formula will be “= value1 + value2 + value3 + so on…”
Examples
We will consider some Add Time in Google Sheets examples.
Example #1 – Subtract Time to get Duration in Google Sheets.
Consider the data table given below that consists of time values in AM and PM. We will find the time difference between the values or Subtract Time to get Duration in Google Sheets.
[Note: We must remember, whenever we calculate the difference of numeric values, we must always subtract the lower value from the higher value.]
The steps to calculate the time difference are,
Step 1: Select cell C2 and enter the formula =B2-A2, as shown below.
Step 2: Press “Enter”. Here, we have got the output, as shown below with PM format in it. However, we need the time duration or time difference and not the time, Therefore, select Column C – select the “Format” tab – click the “Number” option right-arrow à-select the “Duration” option, as shown below.
Step 3: Now, the output will be as shown below, without the PM format, but just the duration.
Step 4: Drag the formula from cell C2 to C6 using the fill handle to get the following results.
Example #2 – Add Minutes to Time in Google Sheets
The table below consists of time values with specific minutes to be added to each of the values. Let us Add Minutes to Time in Google Sheets using the SUM function.
The steps to Add Minutes to Time in Google Sheets are,
Step 1: Select cell C2 and enter the formula =sum(A2,B2), as shown below.
Step 2: Press “Enter” to get the output shown below.
[Note: Google Sheets provides the autofill option. We can select it or use the fill handle option.]
Step 3: Drag the formula from cell C2 to C4 using the fill handle to get the following results, same as the google suggested autofill.
We see that adding minutes to the values gets totalled to the existing values and changes time values from AM to PM or vice versa.
Example #3 – Add Seconds to Time in Google Sheets
The table below consists of a time value for which we will Add Seconds to Time in Google Sheets to see the difference.
The steps to Add Seconds to Time in Google Sheets are,
Step 1: Select cell C2, enter the formula =SUM(A2,B2) and press “Enter”,as shown below.
Step 2: Click the check/tick option and get the following results.
We get the above output, where the seconds values are added to the existing time values.
Example #4 – Find the Difference Between Two Specified Times in Google Sheets
The dataset given below consists of time values for which we will find the time difference using the arithmetic subtraction operator, “-”.
The procedure to Find the Difference Between Two Specified Times in Google Sheets is,
Select cell C2, enter the formula =B2-A2 and press “Enter”, to get the time difference, as shown below.
How To Sum Time In Google Sheets?
We can Sum Time in Google Sheets using the SUM function In Google Sheets in 2 ways, namely,
- Access from the Google Sheets ribbon.
- Enter in the worksheet manually.
Method #1 – Access from the Google Sheets Toolbar-
Step 1: Choose an empty cell for the output –select the “Insert” tab – click the “Function” option right arrow –click the “Math” option right arrow – select the “SUM” function, as shown below.
Step 2: The “SUM” formulaappears, as shown below. Enter the argument as cell values or cell reference.
Method #2 – Enter in the worksheet manually –
Step 1: Select an empty cell for the output.
Step 2: Type = SUM( in the cell, as shown below. [Alternatively, type =S or =SU and double-click the SUM function from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Important Things To Note
- We can apply a Custom/Date/Time format to the formula cell if a calculated time is displayed as a decimal number.
- If a cell displays something like ##### after applying the custom formatting, then this could be because the cell is not wide enough to show the date-time value. So, one can fix this by expanding the column width in Google Sheets.
- We get the “#NUM” error, if there is a negative time value.
- If the result cell format is not valid, then, we will get incorrect results.
Frequently Asked Questions (FAQs)
We often forget in which category a function falls, here, the “SUM” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow – select the “SUM” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the SUM function in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “SUM” function, as shown below.
A few reasons why Add Time in Google Sheets may not work are,
a. The selected cell values have incorrect time format.
b. The cell values may be alpha-numeric.
c. The final value has exceeded 24 hours. Therefore, we must enter a custom time format.
Download Template
This article must help understand Add Time in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is Add Time In Google Sheets. We learn to add time in various ways like using the SUM function along with syntax and examples. You can learn more from the following articles. –
Conditional Formatting Based On Another Cell In Google Sheets
Leave a Reply