## How to Add Time in Excel Formula?

We can add time in excel using the SUM () function, or do individual addition using the ‘+’ operator. However, in addition, Excel ignores the hours that exceed 24. When this is the case, we use Custom Number Format.

### Examples

#### Example #1

If we wish to calculate the total time taken by a student to complete two different assignments, when the student can complete the first assignment in 5 hours and 40 minutes, and the second assignment in 8 hours and 20 minutes:

The output is shown below.

We can see that the two times taken by the student to complete two different assignments are provided in cells: B2 and B3, and we wish to calculate the total time taken by a student in cell B4. So, we sum the two given times in excel using SUM () function and we get the desired result- 14 hours for completion of two assignments. This works quite easily as the given hours do not add up to more than 24.

#### Example #2

Now, let’s say in the above example we wish to sum up more than 24 hours, where the time taken by the student to complete the first assignment is 15 hours and time is taken to complete the second assignment is 12 hours and 30 minutes:

The output is shown below.

We can see that the two times taken by the student to complete two different assignments are provided in cells: B2 and B3, and we wish to calculate the total time taken by the student in cell B4. So, we add the two given time values using excel SUM () function and we get the desired result- 3:30. This is so because the time taken to complete the first assignment is in a 24-hour time format, i.e 15:00 is the same as 03:00. So, to display the time as more than 24 hours, we change its format by following the below steps:

**Step 1:**Click on the ‘Home’ tab and expand the ‘Number Format’ dropdown and**Step 2:**Click on ‘More Number Formats’

**Step 3:**Select ‘Custom’ and in the ‘Type’ box, type:**[h]: mm;@,**and click ‘OK’.

We can see that with this format, the result changes from **03:30 to 27:30**.

Now, this format would be saved in the ‘Type’ list the next time we need it.

#### Example #3

Now, let’s say we wish to add the desired time interval in excel to a given time: To do this, we divide the number of hours, minutes, or seconds by the number of the corresponding unit in one day (24 hours, 1440 minutes, 86400 seconds) and add the resultant quotient to the given time:

**Case #1:** When the time to be added is under 24 hours in excel:

We can see that cell A2 contains an initial time and the number of hours we wish to add to this is contained in cell B2. Now, this addition of two-time values is done as follows:

=Initial Time + ( No of hours to be added/24)

So we see that when 4 hours are added to the time: 12:00, we get the result as 16:00, which is true.

Now, this can also be achieved by using the TIME () function as follows:

=Initial Time + TIME ( No of hours to be added,0,0)

So we see that we get the same result by using TIME () function as well.

However, TIME () function can be used this way only when we wish to add under 24 hours.

**Case #2: **When the time to be added is over 24 hours in Excel:

We can see that cell A4 contains an initial DateTime and the number of hours we wish to add to this is contained in cell B4. Now, this addition of two-time values is done as follows:

=Initial Time + ( No of hours to be added/24)

So we see that this formula has no limitations/restrictions to the number of hours we wish to add. Hence, when 30 hours (>24) are added to the DateTime: ‘20-03-2019 16:49’, we get the result as ‘21-03-2019 22:49’.

**Case #3: **When the excel time to be added is over 60 minutes or 60 seconds:

Now, in this case, the addition of two-time values is done by using the same formula as above. The only difference in the formula is that:

- the number of minutes that we wish to add to the given DateTime is divided by 1440 when time to be added is over 60 minutes (as 1 day has 1440 minutes) and the formula works as follows:

=Initial Time + ( No of minutes to be added/1440)

- the number of seconds that we wish to add to the given DateTime is divided by 86400 when the time to be added is over 60 seconds (as 1 day has 86400 seconds) and the formula works as follows:

=Initial Time + ( No of seconds to be added/86400)

### Things to Remember

- We can apply a Custom/Date/Time format to the formula cell if a calculated time displays as a decimal number.
- In order to enable to automatically format time values which when added together exceed 24 hours, 60 minutes, or 60 seconds, we can use following custom number formats and SUM () function in Excel:

**For Hours** – [h]:mm:ss

**For Minutes** – [m]:ss

**For Seconds** – [ss]

- The Custom hours format is already created in Excel, however, minutes and seconds format needs to be created for workbooks that require them.
- The Custom time formats work only for positive time values.
- Time format applied to a cell would only change its display presentation without any change in the cell value.
- We can sum up the time values first and then format the result as required.
- If a cell displays something like ##### after applying the custom formatting, then this could be because the cell is not wide enough to display the date-time value. So this can be fixed by expanding the column width.

### Recommended Articles

This has been a guide to Add time in Excel. Here we discuss how to Sum Hours, Minutes, and Seconds in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

- How to Subtract Time in Excel?
- Excel VBA Timer Function
- What is the Timeline in Excel?
- How to Insert Date in Excel?

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion