Sum up active times for duplicate user entries. Isolate array and process times.

[AWM21]

Hello,

I have a sheet of data in excel and I need to get the total minutes engaged per user.
Most the users have a single row hosting their start and stop times. (Cell B1=UserName; B2=StartDateTime; B3=StopDateTime). I can get total number of minutes pretty easy from that, so no issue there.
**(DateTime is formatted like this: (2021-06-09 14:02:43)

Where I'm having an issue is that some of the data has duplicate user entries, where each duplicate will have a new start and stop time.

• Example:
  • *Jon was engaged for the entire session, no interruptions. John has a single data row with one start & stop time.
  • *Jane was in and out of the session, so her data shows that she logged in and out several times resulting in many duplicate entries. I would like to isolate Jane's array and sum up her total time in the session.

Now picture I have 1000 users but 150 of them are like Jane & have multiple entries that might result in 6 or 7 duplicate entries per user.
How can I isolate the duplicate entries, add up the date/times by user, and then use that compiled data with the original data sheet?

I feel like/hope this can happen pretty easily with VB.
Thank You!

 

[Eric W]

Welcome to the MrExcel forum!

I'm not clear on your sheet layout, but perhaps something like this:

Book1
	A	B	C	D	E	F	G
1		Name	Start	Stop		Name	Time
2		Jon	2021-06-09 14:02:43	2021-06-09 15:02:49		Jane	0:50:47
3		Jane	2021-06-10 15:01:00	2021-06-10 15:35:55		Jerry	1:00:01
4		Jerry	2021-06-11 16:00:00	2021-06-11 17:00:01		Jon	1:00:06
5		Jane	2021-06-10 15:45:08	2021-06-10 16:01:00
6
Sheet5

Cell Formulas
Range	Formula
F2:F4	F2	=SORT(UNIQUE(B2:B5))
G2:G4	G2	=SUMIF(B:B,F2#,D:D)-SUMIF(B:B,F2#,C:C)
Dynamic array formulas.

 

[AWM21]

Hello Eric! Thank you for the help today. I displayed the cells inaccurately in the above post. Should have been A1=Name; B1=TimeStart; C1=TimeStop.
Sorry for that.

This formula looks like it will work perfectly! I'm excited to get in front of the data and plug it in!
Thanks again!!