Excel formula required on start and finish date-CodePudding

I am looking to find a formula to show a "Start date" based on the first date entry and also "Finish date" if the there is no date against a person's name since last two months. I.e August = left.

see image example of data

CodePudding user response：

This can be done with a simple MINIFS() function, as in following screenshot:

CodePudding user response：

To calculate the Start date use the MINIFS function.

=MINIFS($B$2:$B$17,$A$2:$A$17,$D2)

To calculate the Last Check date use the EDATE function.

=EDATE(TODAY(),-2)

To calculate the Leave date use the MAXIFS function with the results of the EDATE function.
If it returns a zero, then use it again to get the last date for the person or return a blank.

=IF(MAXIFS($B$2:$B$17,$A$2:$A$17,$D2,$B$2:$B$17,">"&$H$2)=0,MAXIFS($B$2:$B$17,$A$2:$A$17,$D2),"")

=IF(MAXIFS($B$2:$B$17,$A$2:$A$17,$D2,$B$2:$B$17,">"&$H$2)=0,MAXIFS($B$2:$B$17,$A$2:$A$17,$D2),"")