Forum Discussion
evilgreenred
Mar 14, 2023Copper Contributor
How to get output for the next appointment date based on admission date in Excel
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?*
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
- evilgreenredCopper ContributorThe formula is not working though. What should I do if I need the date instead?