Finding overlapping times and summarizing results by group?

[JHarmon]

I have a data set that has client names, dates, and time in/out for each employee (xl2bb copied below). I need to be able to find and summarize the overlapping time where there were 2 or more staff serving a client.

I was able to calculate the number of total hours per line in column H. What I need in a separate column, is just the overlapping time in hours. (Column i) I hard coded the values that I'm looking to get (i3, i9) when there are two entries that overlap. I am even more lost when there are multiple lines that overlap (shaded blue rows 10, 11, 13, 14)

Alternatively, if there is a better way to summarize, by date, by client, the number of hours that are done with 1 staff, 2 staff, 3 staff? Something like the second xl2bb example below?

Thank you for any help you can offer!

DATASET

book1.xlsx
	A	B	C	D	E	F	G	H	I
1	Client	Date	Staff	In	Out	Location		TOTAL HOURS	OVERLAP HOURS
2	A	9/16/2022	X	2:30 PM	11:00 PM	Townsend		8.50
3	A	9/16/2022	Y	10:00 PM	12:00 AM	Townsend		2.00	1.000
4	A	9/16/2022	X	1:00 PM	2:30 PM	Townsend		1.50
5	B	9/16/2022	X	12:00 AM	6:00 AM	Townsend		6.00
6	A	9/16/2022	Y	6:00 AM	8:03 AM	Townsend		2.05
7	A	9/17/2022	Y	12:00 AM	7:00 AM	Townsend		7.00
8	B	9/17/2022	X	7:00 AM	8:08 AM	Townsend		1.13
9	B	9/17/2022	Y	7:51 AM	7:58 PM	Townsend		12.12	0.283
10	C	9/18/2022	Z	12:00 AM	9:18 AM	Townsend		9.30
11	C	9/18/2022	Y	8:16 AM	6:40 PM	Townsend		10.40
12	C	9/18/2022	X	11:18 PM	12:00 AM	Townsend		0.70
13	C	9/18/2022	W	8:40 PM	11:11 PM	Townsend		2.52
14	C	9/18/2022	V	4:22 PM	11:14 PM	Townsend		6.87
Sheet1

Cell Formulas
Range	Formula
H2:H14	H2	=MOD(E2-D2,1)*24

Summarized Results

book1.xlsx
	L	M	N	O
1	Client	Date	1:1 Hours	2:1 Hours
2	A	9/16/2022	20.05	1
3	B	9/16/2022	6
4	A	9/17/2022	7
5	B	9/17/2022	12.967	0.283
Sheet1

 

[Sulprobil]

Hello JHarmon,

My suggested solution (download and use at your own risk - but I use an up-to-date virus checking program):

Dropbox - File Deleted - Simplify your life

www.dropbox.com

MrExcel_Finding overlapping times and summarizing results by group.xlsm
	A	B	C	D	E	F
1	Client	Date	Staff	In	Out	Location
2	A	16.09.2022	X	13:00:00	14:30:00	Townsend
3	A	16.09.2022	X	14:30:00	23:00:00	Townsend
4	A	16.09.2022	Y	06:00:00	08:03:00	Townsend
5	A	16.09.2022	Y	22:00:00	00:00:00	Townsend
6	A	17.09.2022	Y	00:00:00	07:00:00	Townsend
7	B	16.09.2022	X	00:00:00	06:00:00	Townsend
8	B	17.09.2022	X	07:00:00	08:08:00	Townsend
9	B	17.09.2022	Y	07:51:00	19:58:00	Townsend
10	C	18.09.2022	V	16:22:00	23:14:00	Townsend
11	C	18.09.2022	W	20:40:00	23:11:00	Townsend
12	C	18.09.2022	X	23:18:00	00:00:00	Townsend
13	C	18.09.2022	Y	08:16:00	18:40:00	Townsend
14	C	18.09.2022	Z	00:00:00	09:18:00	Townsend
Input

MrExcel_Finding overlapping times and summarizing results by group.xlsm
	A	B	C	D
1	Client	Datetime	Staff	Hours [h:mm]
2	A	16.09.2022 06:00:00	Y	2:03
3	A	16.09.2022 08:03:00
4	A	16.09.2022 13:00:00	X	1:30
5	A	16.09.2022 14:30:00	X	7:30
6	A	16.09.2022 22:00:00	X, Y	2:00
7	A	16.09.2022 23:00:00	Y	1:00
8	A	17.09.2022	Y	7:00
9	A	17.09.2022 07:00:00
10	B	16.09.2022	X	6:00
11	B	16.09.2022 06:00:00
12	B	17.09.2022 07:00:00	X	0:51
13	B	17.09.2022 07:51:00	X, Y	0:34
14	B	17.09.2022 08:08:00	Y	11:50
15	B	17.09.2022 19:58:00
16	C	18.09.2022	Z	8:16
17	C	18.09.2022 08:16:00	Y, Z	2:04
18	C	18.09.2022 09:18:00	Y	7:04
19	C	18.09.2022 16:22:00	V, Y	4:36
20	C	18.09.2022 18:40:00	V	2:00
21	C	18.09.2022 20:40:00	V, W	5:02
22	C	18.09.2022 23:11:00	V	0:03
23	C	18.09.2022 23:14:00
24	C	18.09.2022 23:18:00	X	0:42
25	C	19.09.2022
Intermediate

MrExcel_Finding overlapping times and summarizing results by group.xlsm
	A	B	C	D
1	Client	Date	1:1 Hours	2:1 Hours
2	A	16.09.2022	12:03
3	A	16.09.2022		2:00
4	A	17.09.2022	7:00
5	B	16.09.2022	6:00
6	B	17.09.2022	12:41
7	B	17.09.2022		0:34
8	C	18.09.2022	18:05
9	C	18.09.2022		11:42
Output

Option Explicit

Enum Input_Columns
    ic_LBound = 0
    ic_Client
    ic_Date
    ic_Staff
    ic_In
    ic_Out
    ic_Location
    ic_UBound
End Enum

Enum Intermediate_Columns
    imc_LBound = 0
    imc_Client
    imc_Datetime
    imc_Staff
    imc_Hours
    imc_UBound
End Enum

Enum Output_Columns
    oc_LBound = 0
    oc_Client
    oc_Date
    oc_Hours
End Enum

Sub Client_Hours()
'Sulprobil v0.1 14-Oct-2022
Dim dt                     As Date
Dim dtHours                As Date
Dim dtIn                   As Date
Dim dtOut                  As Date
Dim dtStart                As Date
Dim dtEnd                  As Date
Dim i                      As Long
Dim j                      As Long
Dim k                      As Long
Dim oStaff                 As Object
Dim oHours                 As Object
Dim s                      As String
Dim sClient                As String
Dim sClient2               As String
Dim sClient3               As String
Dim sStaff                 As String
Dim v                      As Variant
Dim vSplit                 As Variant

'Initialize
wsIM.Cells.EntireColumn.Delete
wsIM.Range("A1:D1").FormulaArray = Array("Client", "Datetime", "Staff", "Hours [h:mm]")
wsO.Cells.EntireColumn.Delete
wsO.Range("A1:B1").FormulaArray = Array("Client", "Date")
Set oStaff = CreateObject("Scripting.Dictionary")
Set oHours = CreateObject("Scripting.Dictionary")

'First pass through Input - collect in and out datetimes per client
i = 2
sClient = wsI.Cells(i, ic_Client)
Do While sClient <> ""
    oStaff(wsI.Cells(i, ic_Client) & "|" & wsI.Cells(i, ic_Date) + wsI.Cells(i, ic_In)) = 1
    oStaff(wsI.Cells(i, ic_Client) & "|" & wsI.Cells(i, ic_Date) + _
        IIf(wsI.Cells(i, ic_Out) < wsI.Cells(i, ic_In), 1, 0) + wsI.Cells(i, ic_Out)) = 1
    i = i + 1
    sClient = wsI.Cells(i, ic_Client)
Loop
With wsI.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=wsI.Range(wsI.Cells(2, ic_In), wsI.Cells(i - 1, ic_In))
    .SetRange wsI.Range(wsI.Cells(2, ic_LBound + 1), wsI.Cells(i - 1, ic_UBound - 1))
    .Apply
    .SortFields.Clear
    .SortFields.Add2 Key:=wsI.Range(wsI.Cells(2, ic_Client), wsI.Cells(i - 1, ic_Client))
    .SortFields.Add2 Key:=wsI.Range(wsI.Cells(2, ic_Date), wsI.Cells(i - 1, ic_Date))
    .SortFields.Add2 Key:=wsI.Range(wsI.Cells(2, ic_Staff), wsI.Cells(i - 1, ic_Staff))
    .SetRange wsI.Range(wsI.Cells(2, ic_LBound + 1), wsI.Cells(i - 1, ic_UBound - 1))
    .Apply
End With
wsI.Cells.EntireColumn.AutoFit
i = 2
For Each v In oStaff.keys
    wsIM.Cells(i, imc_Client) = Split(v, "|")(0)
    wsIM.Cells(i, imc_Datetime) = Split(v, "|")(1)
    i = i + 1
Next v
With wsIM.Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=wsIM.Range(wsIM.Cells(2, imc_Client), _
        wsIM.Cells(i - 1, imc_Client))
    .SortFields.Add2 Key:=wsIM.Range(wsIM.Cells(2, imc_Datetime), _
        wsIM.Cells(i - 1, imc_Datetime))
    .SetRange wsIM.Range(wsIM.Cells(2, imc_Client), wsIM.Cells(i - 1, imc_Datetime))
    .Apply
End With

'Second pass through Input - collect staff and aggregate hours
With Application.WorksheetFunction
i = 2
sClient = wsI.Cells(i, ic_Client)
sStaff = wsI.Cells(i, ic_Staff)
dtIn = wsI.Cells(i, ic_Date) + wsI.Cells(i, ic_In)
dtOut = wsI.Cells(i, ic_Date) + wsI.Cells(i, ic_Out)
j = 2
Do While sClient <> ""
    k = j
    sClient2 = wsIM.Cells(k, imc_Client)
    dtStart = wsIM.Cells(k, imc_Datetime)
    sClient3 = wsIM.Cells(k + 1, imc_Client)
    dtEnd = wsIM.Cells(k + 1, imc_Datetime)
    Do While sClient = sClient2 And sClient2 = sClient3 And dtStart < dtOut
        If dtStart >= dtIn And dtEnd <= dtOut Then
            wsIM.Cells(k, imc_Staff) = wsIM.Cells(k, imc_Staff) & ", " & sStaff
            wsIM.Cells(k, imc_Hours) = wsIM.Cells(k, imc_Hours) + _
                .Min(dtOut, dtEnd) - .Max(dtIn, dtStart)
        End If
        k = k + 1
        sClient2 = wsIM.Cells(k, imc_Client)
        dtStart = wsIM.Cells(k, imc_Datetime)
        sClient3 = wsIM.Cells(k + 1, imc_Client)
        dtEnd = wsIM.Cells(k + 1, imc_Datetime)
    Loop
    i = i + 1
    sClient = wsI.Cells(i, ic_Client)
    sStaff = wsI.Cells(i, ic_Staff)
    dtIn = wsI.Cells(i, ic_Date) + wsI.Cells(i, ic_In)
    dtOut = wsI.Cells(i, ic_Date) + _
        IIf(wsI.Cells(i, ic_Out) < wsI.Cells(i, ic_In), 1, 0) + wsI.Cells(i, ic_Out)
    If sClient <> "" And sClient <> sClient2 Then
        j = k
        Do While sClient <> wsIM.Cells(j, imc_Client)
            j = j + 1
        Loop
    End If
Loop

'Aggregate hours per client per day and show how many members of staff worked those
j = 0
k = 0
Set oHours = Nothing
Set oHours = CreateObject("Scripting.Dictionary")
i = 2
sClient = wsIM.Cells(i, imc_Client)
Do While sClient <> ""
    dt = wsIM.Cells(i, imc_Datetime)
    sStaff = wsIM.Cells(i, imc_Staff)
    If sStaff <> "" Then
        j = UBound(Split(sStaff, ","))
        If k < j Then k = j
        s = sClient & "|" & Int(dt) & "|" & j
        dtHours = wsIM.Cells(i, imc_Hours)
        oHours(s) = oHours(s) + dtHours
        If Left(sStaff & "  ", 2) = ", " Then
            wsIM.Cells(i, imc_Staff) = Right(sStaff, Len(sStaff) - 2)
        End If
    End If
    i = i + 1
    sClient = wsIM.Cells(i, imc_Client)
Loop
wsIM.Cells.EntireColumn.AutoFit
For i = 1 To k
    wsO.Cells(1, i + 2) = i & ":1 Hours"
Next i
i = 2
For Each v In oHours.keys
    vSplit = Split(v, "|")
    wsO.Cells(i, oc_Client) = vSplit(0)
    wsO.Cells(i, oc_Date) = vSplit(1)
    wsO.Cells(i, oc_Hours + vSplit(2) - 1) = oHours(v)
    i = i + 1
Next v
wsO.Cells.EntireColumn.AutoFit
End With
End Sub

Regards,
Bernd

 

[JHarmon]

Bernd, that worked PERFECTLY. Thank you so much for taking the time to help. I'm not very familiar with VBA, and was trying to get a formula or query to fix this. I had ended up just using some xlookups and filter/sumifs to get a more "summarized" view by client, and then doing some manual calculating for the ratios. Your solution feels like magic. Thank you again!