Apr 16 2021 07:25 AM - edited Apr 16 2021 07:42 AM
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?
Apr 16 2021 07:33 AM
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.
Apr 16 2021 07:42 AM
Apr 16 2021 07:42 AM
Apr 16 2021 07:43 AM
Apr 16 2021 07:57 AM
Apr 16 2021 08:04 AM
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
Apr 16 2021 08:28 AM - edited Apr 16 2021 08:29 AM
SolutionMy 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
Apr 20 2021 01:23 PM
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
Apr 21 2021 01:41 AM
Apr 21 2021 01:50 AM
Attached file with formula, but I think I ruined your date format.
Apr 16 2021 08:28 AM - edited Apr 16 2021 08:29 AM
SolutionMy 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