SOLVED

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

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

4 Replies

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

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

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

The formula is not working though. What should I do if I need the date instead?
best response confirmed by evilgreenred (Copper Contributor)
Solution

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

In what way doesn't it work?

Workbook attached.

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

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

Accepted Solutions
best response confirmed by evilgreenred (Copper Contributor)
Solution

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

In what way doesn't it work?

Workbook attached.