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

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked

For eg:

Source: Add Time in Excel (wallstreetmojo.com)

### 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 were taken by the student to complete two different assignments 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 the SUM () function, and we get the desired result- 14 hours for completion of two assignments. It works quite quickly 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 the time 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. 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**.

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, the 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 the TIME () function as well.

However, the 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:

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 the 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.
- To enable to automatically format time values which, when added together, exceed 24 hours, 60 minutes, or 60 seconds, we can use the following custom number formatsCustom Number FormatsIn Excel, Custom Number Format refers to changing the format of the numeric values as per your preferences to make the data look more visually appealing. This includes formatting large numbers in thousands or millions, formatting dates to only display the month names etc. read more 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, the minutes and seconds format needs to be created for workbooks that require them.
- The Custom time formats work only for positive time values.
- The 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 show the date-time value. So this can be fixed by expanding the column width in excel.

### Recommended Articles

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

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

## Leave a Reply