ExcelHow

How to Add Times Over 24 Hours in Excel

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.
How to Add Times Over 24 Hours 1
  • Total hours is calculated incorrectly when applying SUM for A2 to A6.
How to Add Times Over 24 Hours 2

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.

How to Add Times Over 24 Hours 3

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.

How to Add Times Over 24 Hours 4

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

How to Add Times Over 24 Hours 5

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.

https://youtu.be/NBq4MVaakb0

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],…)…

Recent Posts