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

Copper 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?

2 Replies

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

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)