How to add time in Excel

It may seem to be a very simple idea to add some hours to a time value in Excel, 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.
• 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.
• When we try to add numbers to the time value, then they just don’t get added into time in number format because Excel can’t add two things which are of different data types. Let’s see what happens when we try to add numbers to time.
• We can see that the numbers didn’t get added to the time values and the time remained the same. It is because Excel did not add values of two different data types.

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 Excel 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.

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 to get results.

• The time was added properly for all rows except row 4, where the expected result was 31:15 minutes but we got an erroneous value as 7:15. 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 – Change the format of the result column.

• Select the values in the result column and press CTRL+1.
• This will open up the dialog box to format cells. In this dialog box go the Custom option and write the following format in the Type: field;

[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 OK and you will get all the results as desired.