Excel Functions Tutorials

- Date and Time Function in Excel
- Add Time in Excel
- Subtract Time in Excel
- Date Excel Function
- Date Formula in Excel
- DATEVALUE Excel Function
- DATEDIF in Excel
- Day Excel Function
- Day Excel Formula
- EDATE Excel Function
- EOMONTH
- EOMONTH in Excel
- Excel Add Months to Date
- NETWORKDAYS Excel Function
- Now Excel Function
- Time Excel Function
- TIME Formula in Excel
- Today Excel Function
- Weekday Excel Function
- WORKDAY Excel Function
- Week Number in Excel
- YEAR Function in Excel
- YEAR Formula in Excel

- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)

Related Courses

## How to Add Time in Excel Formula?

With Excel, it becomes quite easy to add or subtract different date and time values. To add time values that are less than 24 hours, we can simply add them 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 of Using Sum Hours in Excel to Add time

Let us understand using some example of Adding Time in excel.

#### Add Time in Excel Example #1

If we wish to calculate 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 total time taken by a student in cell B4. So, we add the two given time 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.

#### Add Time in Excel Example #2

Now, let’s say in the above example we wish to add 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 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 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

4.9 (1,353 ratings)

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

#### Add Time in Excel 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 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 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 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 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 about Add Time in Excel

- 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.
- We can even add hours/minutes/seconds to a Date or Time 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 along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

- Subtract Time in Excel
- Quotient in Excel
- VBA Timer
- VBA Time
- Not Working in Excel Formula (Updating)
- Timeline in Excel
- Timesheet Calculator in Excel
- Insert Date in Excel
- Consolidate Data in Excel

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