How to count the nearest future date based in Patient Index umber

[evilgreenred]

Patient Index Number   Adm Date          Dis Date                Next Date

1                                     27/11/2021       1/12/2021             27/6/2022 

2                                     27/6/2022         1/12/2021             27/6/2022

2                                     4/2/2022           9/2/2021               29/7/2022 

2                                     4/2/2022           9/2/2021               29/7/2022                              

3                                    19/11/2021        23/11/2021            7/7/2022 

3                                     7/7/2022           23/11/2021            7/7/2022 

3                                    29/8/2022           23/11/2021          7/7/2022 

Hi, I would need some help with regards to Excel. Right now, assuming I have patient index number admin date and dis date. Based on the index number, I would like to find the nearest future next date based on the Adm Date given.

For example, from index number = 1, there are two adm Date: 27/11/2021 and 27/6/2022. Since the Dis Date is showing 12/1/2021, the next date would be output as 27/6/2022 since 27/11/2021 is before the date 27/6/2022.

Simiarlity for index number = 3, there are 3 adm Date: 19/11/2021, 7/7/2022 and 29/8/2022, since the Dis Date is showing 23/11/2021, the next date would be output as 7/7/2022 since 19/11/2021 is before 23/11/2021 while 29/8/2022 is later than 7/7/2022.

I would like to use the formula and output as from column D. How should I do it? 

[#2]

Hi

Why 29/7/2022 for 2 ?

=MINIFS($B$2:$B$8,$B$2:$B$8,">="&MAXIFS($C$2:$C$8,$A$2:$A$8,A2),$A$2:$A$8,A2)

@evilgreenred 

[#3]

@evilgreenred 

Reposted as How to get output for the next appointment date based on admission date in Excel