Return next date

[athurmond]

I created a spreadsheet that calculates upcoming well child visits for children in a program. I need to determine each client's next visit due date. What formula can I use?

[#2]

@athurmond 

Who is the "client" in terms of the spreadsheet and what is the logic, which date shall be next due date? Perhaps you may add at least one desired result manually to illustrate.

[#3]

On the spreadsheet, the clients are listed by case # in the first column. Each row lists upcoming well visits for each client. I need the next upcoming visit due date. I will try to add a sample of what I want.

[#4]

This will give you the smalest (next) date that is bigger (later) than today
=MINIFS(Table28[@],Table28[@],">"&TODAY())

[#5]

Look at the spreadsheet now and you will see what I am trying to do.

[#6]

I cannot get that formula to work.

[#7]

@athurmond 

Lost in translation? attached

[#8]

When I download your attachment I can see the formula works correctly. However, when I click "Enable Editing" the value turns to #Name? and no longer shows the date. @MindreVetande 

[#9 · Accepted solution]

@athurmond 

My bad. MINIFS is newer than i remembered. You have to use excel 2019 or newer.

https://support.microsoft.com/en-us/office/minifs-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599

so, let's make an old fashioned complicated solution with AGGREGATE...

attached again

Leila explains how it works in this video (at least i assume it is the right one)

Excel MAX or MIN with CONDITIONS (MAXIFS & AGGREGATE Method) - YouTube

[#10]

That worked! Thank you so much for your help.

[#11]

Hi, the formula works great, but can you update it for this revised spreadsheet? I tried and cannot get it to work. I added more columns I need to include in the formula.@MindreVetande 

[#12]

Hi. You have a mix of table and nontable areas in the new file. If you use traditional references i F2 this should work:
=AGGREGATE(15,6,(G2:AD2)/(G2:AD2>TODAY()),1)
or, if want to hide the errors:
=IFERROR(AGGREGATE(15,6,(G2:AD2)/(G2:AD2>TODAY()),1),"")

Expand the table and Change G2:AD2 to Table-references if you prefer that.

[#13]

@MindreVetande 

Attached file with formula, but I think I ruined your date format.