Computing Last Logout vs Next Login

[kyoyacchi]

Hi guys. Hopefully you could help me complete what I want to achieve.

I have 3 columns A:C

A = Employee Name
B = Timestamp (Date&Time)
C = In/Out

Example:

Agent 1 12/18/2020 7:08:04 AGENT_STATUS/LOGGED_IN
Agent 2 12/18/2020 7:10:47 AGENT_AVAILABILITY/UPDATED
Agent 2 12/18/2020 7:37:25 AGENT_AVAILABILITY/UPDATED
Agent 2 12/18/2020 7:38:32 AGENT_STATUS/WENT_AWAY
Agent 2 12/18/2020 7:39:12 AGENT_STATUS/LOGGED_OUT
Agent 1 12/18/2020 8:22:51 AGENT_STATUS/WENT_AWAY
Agent 2 12/18/2020 8:33:05 AGENT_STATUS/LOGGED_IN
Agent 2 12/18/2020 8:33:14 AGENT_AVAILABILITY/UPDATED

I would love to see the following:
Column D = All of their Logins
Column E = All of their Logouts
Column F = Duration between their Last Logout vs Next Login

I'm currently using a Google Spreadsheet.

Reddit Employee TimeStamps

Raw Data Agent,Occurred At,Type,Available For,Away Reason,AGENT_STATUS/LOGGED_IN,AGENT_STATUS/LOGGED_OUT,Duration John Smith,12/18/2020 0:00:05,AGENT_AVAILABILITY/UPDATED,[],John Smith,, John Smith,12/18/2020 0:00:12,AGENT_AVAILABILITY/UPDATED,["MAIL","TASK"],, John Smith,12/18/2020 0:00:15,AGEN...

docs.google.com

 

[maabadi]

Welcome to MrExcel Message Board.
Try this:
After Insert Formula Press CTRL+SHIFT+ENTER
Change C14 Number format to Custom and write at type: [h]:mm:ss

Book1
	A	B	C	D
1	Name	TimeLine	STATUS	IN/OUT
2	Agent 1	Saturday, December 19, 2020 11:34:29 AM	AGENT_STATUS	LOGGED_IN
3	Agent 2	Saturday, December 19, 2020 11:54:29 AM	AGENT_AVAILABILITY	UPDATED
4	Agent 2	Saturday, December 19, 2020 12:34:29 PM	AGENT_AVAILABILITY	UPDATED
5	Agent 2	Saturday, December 19, 2020 12:54:29 PM	AGENT_STATUS	WENT_AWAY
6	Agent 2	Saturday, December 19, 2020 1:24:29 PM	AGENT_STATUS	LOGGED_OUT
7	Agent 1	Saturday, December 19, 2020 2:34:29 PM	AGENT_STATUS	WENT_AWAY
8	Agent 2	Saturday, December 19, 2020 3:34:29 PM	AGENT_STATUS	LOGGED_IN
9	Agent 2	Saturday, December 19, 2020 4:34:29 PM	AGENT_AVAILABILITY	UPDATED
10
11
12	Agent 2	LOGGED_OUT	1:24:29 PM
13	Agent 2	LOGGED_IN	3:34:29 PM
14			2:10:00
Sheet1

Cell Formulas
Range	Formula
C12:C13	C12	=INDEX($A$2:$D$9,MATCH(1,(A12=$A$2:$A$9)*(B12=$D$2:$D$9),0),2)
C14	C14	=C13-C12
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maabadi]

If you want to calculate for Specific Date and your Time and Date is in one Cell and your criteria for Date only has date. Use This:

Book1
	A	B	C	D
1	Name	TimeLine	STATUS	IN/OUT
2	Agent 1	Saturday, December 19, 2020 11:34:29 AM	AGENT_STATUS	LOGGED_IN
3	Agent 2	Saturday, December 19, 2020 11:54:29 AM	AGENT_AVAILABILITY	UPDATED
4	Agent 2	Saturday, December 19, 2020 12:34:29 PM	AGENT_AVAILABILITY	UPDATED
5	Agent 2	Saturday, December 19, 2020 12:54:29 PM	AGENT_STATUS	WENT_AWAY
6	Agent 2	Saturday, December 19, 2020 1:24:29 PM	AGENT_STATUS	LOGGED_OUT
7	Agent 1	Saturday, December 19, 2020 2:34:29 PM	AGENT_STATUS	WENT_AWAY
8	Agent 2	Saturday, December 19, 2020 3:34:29 PM	AGENT_STATUS	LOGGED_IN
9	Agent 2	Saturday, December 19, 2020 4:34:29 PM	AGENT_AVAILABILITY	UPDATED
10	Agent 1	Sunday, December 20, 2020 11:34:29 AM	AGENT_STATUS	LOGGED_IN
11	Agent 2	Sunday, December 20, 2020 11:54:29 AM	AGENT_AVAILABILITY	UPDATED
12	Agent 2	Sunday, December 20, 2020 12:34:29 PM	AGENT_AVAILABILITY	UPDATED
13	Agent 2	Sunday, December 20, 2020 12:54:29 PM	AGENT_STATUS	WENT_AWAY
14	Agent 2	Sunday, December 20, 2020 1:24:29 PM	AGENT_STATUS	LOGGED_OUT
15	Agent 1	Sunday, December 20, 2020 2:34:29 PM	AGENT_STATUS	WENT_AWAY
16	Agent 2	Sunday, December 20, 2020 3:34:29 PM	AGENT_STATUS	LOGGED_IN
17	Agent 2	Sunday, December 20, 2020 4:34:29 PM	AGENT_AVAILABILITY	UPDATED
18
19
20	Agent 2	LOGGED_OUT	1:24:29 PM	12/20/2020
21	Agent 2	LOGGED_IN	3:34:29 PM	12/20/2020
22			2:10:00
Sheet1

Cell Formulas
Range	Formula
C20:C21	C20	=INDEX($A$2:$D$17,MATCH(1,(A20=$A$2:$A$17)*(B20=$D$2:$D$17)*(D20=INT($B$2:$B$17)),0),2)
C22	C22	=C21-C20
Press CTRL+SHIFT+ENTER to enter array formulas.