How to get Login/Logout Times for users with multiple login/logouts on the multiple days

[leenvr]

Hi,

Needing some help to be able to find the initial login and last logout for our staff.
I tried to use the following formula's but only returns with initial login and last logout across the dates.

{=MIN(IF(Sheet2!$A2:$A1000=Sheet1!$A$1,Sheet2!$C2:$C1000,MAX(Sheet2!$C2:$C1000)))}
{=MAX(IF(Sheet2!$A2:$A1000=Sheet1!$A$1,Sheet2!$C2:$C1000,MIN(Sheet2!$C2:$C1000)))}

So after applying the formula it returns to this

Staff A
	Login	Logout
1/02/2024​	31/01/2024 15:04​	9/02/2024 10:23​
2/02/2024​	31/01/2024 15:04​	9/02/2024 10:23​
3/02/2024​	31/01/2024 15:04​	9/02/2024 10:23​
4/02/2024​	31/01/2024 15:04​	9/02/2024 10:23​
5/02/2024​	31/01/2024 15:04​	9/02/2024 10:23​

I am aiming to get similar to this one.

Staff A
Date	Login	Logout
1/02/2024​	1/02/2024 5:59​	1/02/2024 15:02​
2/02/2024​	2/02/2024 5:56​	2/02/2024 15:04​
3/02/2024​
4/02/2024​
5/02/2024​	5/02/2024 6:04​	5/02/2024 15:01​

My source data is the following:

Staff Name	Log In	Log Out
Staff A		31/01/2024 15:04​
Staff A	1/02/2024 5:59​	1/02/2024 13:38​
Staff A	1/02/2024 13:39​	1/02/2024 15:02​
Staff A	2/02/2024 5:56​	2/02/2024 7:10​
Staff A	2/02/2024 7:10​	2/02/2024 8:16​
Staff A	2/02/2024 8:18​	2/02/2024 12:23​
Staff A	2/02/2024 12:23​	2/02/2024 14:21​
Staff A	2/02/2024 14:37​	2/02/2024 15:04​
Staff A	5/02/2024 6:04​	5/02/2024 10:48​
Staff A	5/02/2024 10:49​	5/02/2024 15:01​

Hopefully can get some help. Thank you in advance.

 

[Bedlam]

Staff Name	Log In	Log Out	Int Log In	Int Log Out		Date	Min Log In	Max Log Out
Staff A		31-Jan-2024 15:04​		31 January 2024​		31 January 2024​	#N/A​	31-Jan-2024 15:04​
Staff A	01-Feb-2024 05:59​	01-Feb-2024 13:38​	01 February 2024​	01 February 2024​		01 February 2024​	01-Feb-2024 05:59​	01-Feb-2024 15:02​
Staff A	01-Feb-2024 13:39​	01-Feb-2024 15:02​	01 February 2024​	01 February 2024​		02 February 2024​	02-Feb-2024 05:56​	02-Feb-2024 15:04​
Staff A	02-Feb-2024 05:56​	02-Feb-2024 07:10​	02 February 2024​	02 February 2024​		03 February 2024​	#N/A​	#N/A​
Staff A	02-Feb-2024 07:10​	02-Feb-2024 08:16​	02 February 2024​	02 February 2024​		04 February 2024​	#N/A​	#N/A​
Staff A	02-Feb-2024 08:18​	02-Feb-2024 12:23​	02 February 2024​	02 February 2024​		05 February 2024​	05-Feb-2024 06:04​	05-Feb-2024 15:01​
Staff A	02-Feb-2024 12:23​	02-Feb-2024 14:21​	02 February 2024​	02 February 2024​
Staff A	02-Feb-2024 14:37​	02-Feb-2024 15:04​	02 February 2024​	02 February 2024​
Staff A	05-Feb-2024 06:04​	05-Feb-2024 10:48​	05 February 2024​	05 February 2024​
Staff A	05-Feb-2024 10:49​	05-Feb-2024 15:01​	05 February 2024​	05 February 2024​

Assuming Data is in Cells A1 to C11:
D2 = =IF(INT(CHOOSECOLS(B2:B1000,1))=0,"",INT(CHOOSECOLS(B2:B1000,1)))
E2=IF(INT(CHOOSECOLS(C2:C1000,1))=0,"",INT(CHOOSECOLS(C2:C1000,1)))
G2= =SEQUENCE((INT(MAX(B:B))-MIN(D:D,E:E)+1),1,MIN(D:D,E:E),1)
H2 = =IF(MIN(CHOOSECOLS(FILTER(B2:D1000,D2:D1000=$G2,"No data"),1))=0,NA(),MIN(CHOOSECOLS(FILTER(B2:D1000,D2:D1000=$G2,"No data"),1))) [Drag Down]
I2 = =IF(MAX(CHOOSECOLS(FILTER(C2:E1000,E2:E1000=$G2,"No data"),1))=0,NA(),MAX(CHOOSECOLS(FILTER(C2:E1000,E2:E1000=$G2,"No data"),1))) [Drag Down]

 

[leenvr]

Thank you

 

[leenvr]

Staff Name	Log In	Log Out	Int Log In	Int Log Out		Date	Min Log In	Max Log Out
Staff A		31-Jan-2024 15:04​		31 January 2024​		31 January 2024​	#N/A​	31-Jan-2024 15:04​
Staff A	01-Feb-2024 05:59​	01-Feb-2024 13:38​	01 February 2024​	01 February 2024​		01 February 2024​	01-Feb-2024 05:59​	01-Feb-2024 15:02​
Staff A	01-Feb-2024 13:39​	01-Feb-2024 15:02​	01 February 2024​	01 February 2024​		02 February 2024​	02-Feb-2024 05:56​	02-Feb-2024 15:04​
Staff A	02-Feb-2024 05:56​	02-Feb-2024 07:10​	02 February 2024​	02 February 2024​		03 February 2024​	#N/A​	#N/A​
Staff A	02-Feb-2024 07:10​	02-Feb-2024 08:16​	02 February 2024​	02 February 2024​		04 February 2024​	#N/A​	#N/A​
Staff A	02-Feb-2024 08:18​	02-Feb-2024 12:23​	02 February 2024​	02 February 2024​		05 February 2024​	05-Feb-2024 06:04​	05-Feb-2024 15:01​
Staff A	02-Feb-2024 12:23​	02-Feb-2024 14:21​	02 February 2024​	02 February 2024​
Staff A	02-Feb-2024 14:37​	02-Feb-2024 15:04​	02 February 2024​	02 February 2024​
Staff A	05-Feb-2024 06:04​	05-Feb-2024 10:48​	05 February 2024​	05 February 2024​
Staff A	05-Feb-2024 10:49​	05-Feb-2024 15:01​	05 February 2024​	05 February 2024​

Assuming Data is in Cells A1 to C11:
D2 = =IF(INT(CHOOSECOLS(B2:B1000,1))=0,"",INT(CHOOSECOLS(B2:B1000,1)))
E2=IF(INT(CHOOSECOLS(C2:C1000,1))=0,"",INT(CHOOSECOLS(C2:C1000,1)))
G2= =SEQUENCE((INT(MAX(B:B))-MIN(D:D,E:E)+1),1,MIN(D:D,E:E),1)
H2 = =IF(MIN(CHOOSECOLS(FILTER(B2:D1000,D2:D1000=$G2,"No data"),1))=0,NA(),MIN(CHOOSECOLS(FILTER(B2:D1000,D2:D1000=$G2,"No data"),1))) [Drag Down]
I2 = =IF(MAX(CHOOSECOLS(FILTER(C2:E1000,E2:E1000=$G2,"No data"),1))=0,NA(),MAX(CHOOSECOLS(FILTER(C2:E1000,E2:E1000=$G2,"No data"),1))) [Drag Down]

Thank you