Calculating time from 2 columns

odrisso

New Member
Joined
Oct 15, 2014
Messages
11
Hi,

I have two columns like this way.

a102103
a135136
a157160
b189191
b201202
a222223
a245250
b290292

<tbody>
</tbody>


Here the values are in seconds. I want to write a formula, which show the total duration of seconds of a and b.

For example: here total duration of a is = (160-102) + (250-222) = 86 seconds.

Please suggest me how to do it.

Thanks
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,328
Hi,

I started with some formulas then tried PivotTables but ended up with a macro.
So here it is if a macro solution is acceptable:
Code:
' Remember to add Reference = Microsoft Scripting Runtime
Sub Total_Times_for_Groups()

    Dim Groups As Range, Group As Range
    Dim Totals As Dictionary
    Dim PrevGroup As String, NextGroup As String
    Dim MinValue As Long, MaxValue As Long, Diff As Long
    
    ' Dictionary to store Group name and running total
    Set Totals = New Dictionary

    ' Set Range for List of Groups
    With Sheet1
        Set Groups = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With

    For Each Group In Groups
        ' Note next and previous group names
        PrevGroup = Group.Offset(-1, 0)
        NextGroup = Group.Offset(1, 0)
        
        ' If first entry in Group
        If Group <> PrevGroup Then
            MinValue = Group.Offset(0, 1)
            MaxValue = Group.Offset(0, 2)
        End If
        
        ' If in same group as last tim round the loop
        If Group = PrevGroup Then
            MaxValue = Group.Offset(0, 2)
        End If
        
        ' If last entry in Group
        If Group <> NextGroup Then
            ' Calculate difference f max and min
            Diff = MaxValue - MinValue
            ' Store running total for Group
            With Totals
                If .Exists(Group.Value) Then .Item(Group.Value) = .Item(Group.Value) + Diff Else .Add Group.Value, Diff
            End With
        End If

    Next

    ' Output the answers to the worksheet
    Range("E:F").ClearContents
    With Totals
        For i = 0 To .Count - 1
            Range("E" & i + 1) = .Keys(i)
            Range("F" & i + 1) = .Items(i)
        Next
    End With

End Sub

Basically, it uses a Dictionary to keep a list of groups and totals. A Dictionary in VBA has a Key and an Item. I use the Key to store the Group name and the Item to keep a running total of the time differences.

Then it starts at row 2 and keeps track of the first and last times to get the minimum and maximum values. When it moves on to the next Group it writes the data to the Dictionary.

At the end, the contents of the dictionary are written into columns E and F.
Please Note: The macro clears the contents of these columns so you could lose some data if you have any in there.

The macro needs to be pasted into a macro Module and you also need to go into the Tools-->References menu and tick "Microsoft Scripting Runtime" to make the Dictionary object available.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,171
Messages
5,600,124
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top