Forum Discussion

evilgreenred's avatar
evilgreenred
Copper Contributor
Mar 14, 2023

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? 

 

Resources