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

[evilgreenred]

Hi, I would need some help with regards to Excel as I am still stuck with this problem. Right now, assuming I have patient index number, adm. 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 1/12/2021, the next appointment date would be output as 27/6/2022 since 27/11/2021 is before the date of*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.



Lastly, for index number = 4, since there are only 1 adm Date: 21/7/2022, the output would be the same which is 21/7/2022.



Hoe should I create the excel formula that shows something like in column D, apperciate if someone could help since I cam stuck for very long.

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

[#2]

@evilgreenred 

In D2:

=IF(COUNTIF($A$2:$A$9,A2)=1,B2,MINIFS($B$2:$B$9,$A$2:$A$9,A2,$B$2:$B$9,">="&C2))

Fill down

[#3]

The formula is not working though. What should I do if I need the date instead?

[#4 · Accepted solution]

@evilgreenred 

In what way doesn't it work?

Workbook attached.

[#5]

Sorry, manage to work now. I think I set the wrong date using D/M/Y instead of M/D/Y.