Detect employee actual entry data and exit date

Hi everyone,

I have a employee database, structure as below:
- Each movement of employees will be created as new row in database
Eg. Employee A, move from team "Engineering 1" to "Data Science" with the entry date = exit date + 1

- Blank cell in Exit date means Active employee
- Employee "E" left, then came back after several months

Now, I want to have the actual entry date and actual exit date as what column F and G show.
Could anyone please help? Thank you so much.

Sample Data:

Screenshot 2024-09-30 at 14.48.08.png

Clean All expected results.

F2

=LET(
  a,COUNTA(A2:A100),
  b,A2:C100,
  c,IF((A2:INDEX(b,a,1)=A3:INDEX(b,a+1,1))*(B2:INDEX(b,a,2)=C3:INDEX(b,a+1,3)+1),"",B2:INDEX(b,a,2)),
  DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(x,y,VSTACK(x,IF(INDEX(c,y)="",XLOOKUP(TRUE,DROP(c,y)<>"",DROP(c,y)),INDEX(c,y))))),1)
)

G2

=LET(
  a,COUNTA(A2:A100),
  b,A2:C100,
  c,IF((A2:INDEX(b,a,1)=A1:INDEX(b,a-1,1))*(C2:INDEX(b,a,3)+1=B1:INDEX(b,a-1,2)),"",C2:INDEX(b,a,3)),
  d,SCAN(,c,LAMBDA(x,y,IF(y="",x,y))),
  IF(d=0,"",d)
)

thank you so much, it's fantastic

here another issue, when i change it as a Table format, the formula returns #SPILL!, I checked google for the error, but dont know how to fix it, could you please help?

F2

=LET(
  a,COUNTA($A$2:$A$100),
  b,$A$2:$C$100,
  c,IF(($A$2:INDEX(b,a,1)=$A$3:INDEX(b,a+1,1))*($B$2:INDEX(b,a,2)=$C$3:INDEX(b,a+1,3)+1),"",$B$2:INDEX(b,a,2)),
  d,DROP(REDUCE("",SEQUENCE(ROWS(c)),LAMBDA(x,y,VSTACK(x,IF(INDEX(c,y)="",XLOOKUP(TRUE,DROP(c,y)<>"",DROP(c,y)),INDEX(c,y))))),1),
  INDEX(d,ROW()-1)
)

G2

=LET(
  a,COUNTA($A$2:$A$100),
  b,$A$2:$C$100,
  c,IF(($A$2:INDEX(b,a,1)=$A$1:INDEX(b,a-1,1))*($C$2:INDEX(b,a,3)+1=$B$1:INDEX(b,a-1,2)),"",$C$2:INDEX(b,a,3)),
  d,SCAN(,c,LAMBDA(x,y,IF(y="",x,y))),
  e,IF(d=0,"",d),
  INDEX(e,ROW()-1)
)

Try,
Actual Entry Date:
=IF(ISNUMBER(MATCH([@[Entry Date]]-1,FILTER([[Exit Date ]],[[Employee Name ]]=[@[Employee Name ]]),0)),MINIFS([Entry Date],[[Employee
Name ]],[@[Employee Name ]]),[@[Entry Date]])

Actualy Exit Date:
=IF(ISNUMBER(MATCH([@[Exit Date ]]+1,FILTER([Entry Date],[[Employee Name ]]=[@[Employee Name ]]),0)),IF(AGGREGATE(15,6,$C$2:$C$11/($A$2:$A$11=$A2),1)=0,"",MAXIFS($C$2:$C$11,$A$2:$A$11,$A2)),IF([@[Exit Date ]]=0,"",[@[Exit Date ]]))

Hi Josephteh, I am checking several answers, and there's an error if I add another row data (see row 10), the entry date it takes from the old contract, could you please check it and show me how to fix?

thank you so much everyone
all solutions are greatt

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Change both formulas to:

Actual Entry Date:
=IF(ISNUMBER(MATCH([@[Entry Date]]-1,FILTER([[Exit Date ]],[[Employee Name ]]=[@[Employee Name ]]),0)),MIN(FILTER([Entry Date],([[Employee Name ]]=[@[Employee Name ]])*ISNUMBER(MATCH([[Exit Date ]],[Entry Date]-1,0)))),[@[Entry Date]])

Actual Exit Date:
=IFERROR(1/(1/IF(ISNUMBER(MATCH([@[Exit Date ]]+1,FILTER([Entry Date],[[Employee Name ]]=[@[Employee Name ]]),0)),MAX(FILTER([[Exit Date ]],([[Employee Name ]]=[@[Employee Name ]])*(ISNUMBER(MATCH([[Exit Date ]],[Entry Date]-1,0))=FALSE))),[@[Exit Date ]])),"")

thank you so much

You are welcome. Thanks for the Rep.

Try Exit Date:

=IFERROR(1/(1/IF(ISNUMBER(MATCH([@[Exit Date ]]+1,FILTER([Entry Date],[[Employee Name ]]=[@[Employee Name ]]),0)),MAX(1/(1/(FILTER([[Exit Date ]],([[Employee Name ]]=[@[Employee Name ]])*(ISNUMBER(MATCH([[Exit Date ]],[Entry Date]-1,0))=FALSE))))),[@[Exit Date ]])),"")

It's still not working :(
E11 is incorrect
and also, when i input the exit date for employee A at E2, it leads to the error in E3 & E4

Please check attached workbook.

Thank you josephteh, it works
however, my sample doesn't cover all scenario :(, so the formula is still incorrect after i input employee V as a new sample, could you please check it again? I am sorry and highly appreciate for your help T_T

yes, my bad for not include all scenarios before, so sorry about it
and I will update my Excel version soon, thanks AliGW

Try this in E2.

=IF([@[Exit Date ]]="","",IF(([@[Employee Name ]]=INDEX([[Employee Name ]],ROW()-2))*(INDEX([Entry Date],ROW()-2)=[@[Exit Date ]]+1),IFERROR(1/(1/(INDEX([Exit Date],ROW()-2))),""),[@[Exit Date ]]))

Thanks windknife for coming to the rescue. I have probably complicated matters by trying to come up with a formula to ensure it will work even if the data are not in entry date order.

@josephteh, you are welcome. I just have an idea to solve it.
But I’m not sure if it meets the requirements of the original post.

thank you both of you

You are welcome.
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.