SOLVED

# Return next date

Occasional Contributor

# Return next date

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?

12 Replies

# Re: Return next date

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.

# Re: Return next date

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.

# Re: Return next date

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

# Re: Return next date

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

# Re: Return next date

I cannot get that formula to work.

# Re: Return next date

Lost in translation? attached

# Re: Return next date

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

best response confirmed by athurmond (Occasional Contributor)
Solution

# Re: Return next date

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

# Re: Return next date

That worked! Thank you so much for your help.

# Re: Return next date

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

# Re: Return next date

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.

# Re: Return next date

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