When calculating the total working hours or total efforts in our work, we can use SUM function to calculate total hours conveniently. But when you applying this function in you daily work, you may notice that it only works to calculate total hours less than 24 hours, if the total hours are greater than 24 hours, it cannot work normally and we may get an improper returned value.

See example below.

- Total hours is calculated correctly when applying
**SUM**for A2 and A3.

- Total hours is calculated incorrectly when applying
**SUM**for A2 to A6.

Table of Contents

## 1. Add Times Over 24 Hours by Format Cells Feature

**Step1:** Enter the formula **=SUM(A2:A6)** in B3 to get total hours for provided working hours. Refer to above screenshot we can get the returned value 12:46, obviously it is incorrect.

**Step2:** Select this cell, right click to load options, select **Format Cells** in the last section.

**Step3:** On **Format Cells** panel, under **Number** tab, select **Custom **in **Category** list, in **Type** textbox type ‘**[h]:mm**’, after typing you can see in **Sample** section ’**36:46**’ is displayed. If your time format is hh:mm:ss in working hours, you can enter ‘**[h]:mm:ss**’ in Type.

**Step4:** Click **OK**. Verify that total hours for A2:A6 is updated properly.

## 2. Add Times Over 24 Hours using VBA Code

Our second method involves creating a user-defined function using VBA to precisely accumulate total working hours. Let’s delve into the steps:

**Step1: **Press** Alt + F11** to open the Visual Basic for Applications (VBA) editor.

**Step2: **In the VBA editor, go to **Insert **> **Module **to insert a new module.

**Step3: **Copy and paste the provided VBA code into the module.

```
Function CountTotalWorkingHours(rng As Range) As String
Dim cell As Range
Dim totalHours As Double
Dim totalMinutes As Double
For Each cell In rng
Dim timeParts() As String
timeParts = Split(cell.Text, ":")
If UBound(timeParts) = 1 Then
totalHours = totalHours + Val(timeParts(0))
totalMinutes = totalMinutes + Val(timeParts(1))
End If
Next cell
' Convert excess minutes to hours
totalHours = totalHours + totalMinutes \ 60
totalMinutes = totalMinutes Mod 60
CountTotalWorkingHours = Format(totalHours, "00") & ":" & Format(totalMinutes, "00")
End Function
```

**Step4: **Close the VBA editor by clicking the close button or pressing **Alt + Q**.

**Step5: **In a cell, type:

` =CountTotalWorkingHours(A2:A6)`

adjust the range as per your data

**Step6: **Press **Enter**, and the cell will display the total working hours, accurately counting values beyond 24 hours.

This code accumulates hours and minutes separately, converts excess minutes to hours, and then formats the result as “**hh:mm**“.

## 3. Video: Add Times Over 24 Hours

Welcome to this Excel video tutorial, we’re delving into the intricacies of counting total working hours, surpassing the conventional 24-hour clock. Join us as we explore two dynamic methods—one harnessing the power of Excel’s SUM function for effortless calculations and the other unleashing the capabilities of VBA with a user-defined function.

## 4. Related Functions

- Excel SUM function

The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…