Overlap of date and time

Hello,

Excel help please! I'm trying to calculate the total time someone was in attendance, the report I have shows overlapping dates/times. I need to calculate the overall time in attendance down to the minute. Below is a sample of what I am working from, this is a multiple day event so I would need total time for all days combined.

Thank you!

Day Email Start Time Leave Time Email Total Time
Day 1 Snowwhite111 6/8/2022 8:57 6/8/2022 10:39 Snowwhite111
Day 1 Snowwhite111 6/8/2022 8:02 6/8/2022 8:51 Sleepyabc
Day 1 Snowwhite111 6/8/2022 8:52 6/8/2022 8:57 Docnyc
Day 2 Snowwhite111 6/9/2022 8:57 6/9/2022 10:39 Grumpy367
Day 1 Grumpy367 6/8/2022 8:51 6/8/2022 10:39
Day 1 Grumpy367 6/8/2022 8:02 6/8/2022 8:51
Day 2 Grumpy367 6/9/2022 8:02 6/9/2022 9:51
Day 1 Docnyc 6/8/2022 8:02 6/8/2022 10:39
Day 1 Docnyc 6/8/2022 8:05 6/8/2022 10:04
Day 1 Docnyc 6/8/2022 10:04 6/8/2022 10:39
Day 2 Docnyc 6/9/2022 8:04 6/9/2022 10:39
Day 1 Sleepyabc - -
Day 2 Sleepyabc - -

Edit - added attachment.

Welcome to the forum.

Your workbook should include some manually calculated results to show us what you want, so this is a guess:

=SUMPRODUCT(($B$2:$B$14=H2)*($D$2:$D$14-$C$2:$C$14))

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	H	I
2	Snowwhite111	04:17:32
3	Sleepyabc	00:00:00
4	Docnyc	07:46:52
5	Grumpy367	04:25:27

Sheet: Details

This is why you should add expected results and explanatory notes to your sample workbook. Can you do this now, please?

Thank you, I hope the attached is what you are looking for.

Here's how I did it. I hope I can explain it so it makes sense:

1) You didn't offer an algorithm, so I made one up. The basic idea is that, if I have the time stamps sorted in ascending order and I know which are start and which are leave, then I can start at the "top" and turn "accumulation" on at the right start times and turn "accumulation" off at the right leave times.
2) First step is to unpivot the existing table, then sort by name first, then date/time stamp, and then start or leave (columns M, N, O in attached).
3) Since I need to be able to see pairs of rows, I add a helper column in column P that pairs rows up. The formula here is =IF(M3=M2,O2,"")&O3. This column can take on 5 possible values:
s -- means start of a new person/email. start remembering earliest start time of the first set.
ss -- means two start times in a row. When there are multiple start times in a row, we need to remember the earliest start time of the set.
sl -- change from start to leave time. start remembering leave times until the end of this leave set.
ll -- means two leave times in a row. When there are multiple leave times in a row, we need to accumulate until the last leave time of the set.
ls -- change from leave to start. start remembering earliest start time of this start set.
4) Column Q will be a series of nested IF() functions to execute the logic described above. =IF(P3="s",N3,IF(P3="ss",Q2,IF(P3="sl",Q2+N3,IF(P3="ll",Q2+N3-N2,IF(P3="ls",Q2-N3,NA()))))). The basic arithmetic is to sum up the appropriate leave times and then subtract the sum of the appropriate start times. Note that I chose to format as general rather than time here because Excel (unlike other spreadsheets) does not like to display negative times. General formatting doesn't have trouble showing positive or negative values here.
5) The total time online for each person is the last entry associated with their name. A simple lookup function (I used VLOOKUP()) can be used to extract the desired value.

Is that useful?