Forum Discussion
evilgreenred
Mar 14, 2023Copper Contributor
How to count the nearest future date based in Patient Index umber
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?
- HecatonchireIron Contributor
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)