How to add time in Google Sheets

It may seem to be a very simple idea to add some hours to a time value in Google Sheets, however, it is not as simple as it may seem. In this tutorial we’ll learn how to add hours to a time value and how to add times for different tasks in this tutorial.

Example 1: Adding Hours to a Time Value

Consider this data set in which we have some time values marked as Start Time and we will calculate the End Time by adding some hours to the start time.

Before learning how to add numbers to the time specific value, we’ll try to understand the problem associated with adding simple numbers to time values. So let’s do this step by step.

Step 1 – Adding numbers to time to see the problem

• The first column in our data set is formatted as time and we can see that the day and night time is differentiated by AM and PM properly. When we try to add numbers to the time value, then they just don’t get added into time in number format because Google Sheets can’t add two things which are of different data types. Let’s see what happens when we try to add numbers to time by simple addition.

The problem associated with adding hours to a time value is that if we simply add a number considering that it is time then it won’t be added properly to the time value and we’ll get the incorrect results.

Step 2 – Format Numbers to reflect time values

• So to resolve the problem faced in the last step, we’ll have to format i.e. write the numbers in proper format so that Google Sheets can recognize the numbers as time values.
• Let’s do this by writing all numbers in the format shown above.

Step 3 – Add formatted number to time values

• Now that we have formatted the values properly, we can add these into time values without any further ado. Now all values were added properly with start time values.

Example 2: Adding Times for Different Tasks

In this example we’ll see if we have time to complete various tasks and we would like to add these time values then how we’ll do it. Let’s see the following data set above picture.

Step 1 – Add the time values

• In this case as both values are already in time format so we can add them very easily. Let’s add these values by a simple addition formula to get results which is;

=A2 + B2

The time was added properly for all rows except row 4, where the expected result was 34:45 minutes but we got an erroneous value as 10:45. This happened because the resultant time exceeded 24 hours. To resolve this issue we’ll have to change the format of our result column.

Step 2 – Go to Custom number format in Format options

• Select the values in the result column and Format Tab on the list of main tabs.
• Click on 123 Number arrow and then click on the Custom number format to change the time format so that the correct results will be displayed.

Step 3 – Define a custom format to display results correctly

• This will open up the custom number format dialog box. In this dialog box write the following format in the format text field next to the Apply button.

[h]:mm;@

• Please note very clearly that there is a colon (:) after [h] and a semicolon (;) after mm. It is necessary to write the above format as it is otherwise it will not work. 
• After writing this press the Apply button and you will get all the results as desired.