Formula to return the latest date of a task if status is complete

DG Consulting

Looking for help with a sheet summary formula that will return the last Finish date of a particular task once it is complete. I tried combining If and Max(collect) but am getting an #Unparseable error.

=If(Status@row<>"Complete","",Max(Collect(Finish:Finish, [Task Name]:[Task Name], "Task 4")))

In the below example, the result would be blank since the last Task 4 is not yet complete. But if it were complete the result would be 5/23.

Paul Newcome

That is because there is nothing for that first COLLECT to pull. Try an IFERROR.

=IFERROR(IF(MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4", Status:Status, "Complete")) = MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), "")

Mike TV

@DG Consulting

Your IF at the beginning is just another range and criteria. Add it into the COLLECT ranges and criteria.

Mike TV

@DG Consulting

Here's an example. Column4 row 1 has the formula in it. However, it's not returning blank because the correct result for the criteria and data you've given is 03/28/2023.

Column 4 Row 1 formula:

=MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4", Status:Status, "Complete"))

DG Consulting

@Mike TV Thanks for the response -- but it's not quite what I'm looking for. There are multiple "Task 4" tasks in the schedule, I am looking for a result when the LAST Task 4 is complete. This formula seems to return the last result of whatever Task 4 is complete. The result I'm looking for in the above data is blank since 5/23 is the last Task 4 in the schedule and it is not yet complete. That is why I thought it needed to be an if statement, but maybe there's a better way. I'm looking to use this sheet summary formula on multiple complex schedules which is why I'm hoping it can be a formula that can be applied to multiple schedules. Thanks!

Mike TV

@DG Consulting

Then why don't you use something simple like:

=IF([Status]$23="Complete", [Finish]$23, "")

This would assume row 23 is your final Task 4 at the very end of the project. The absolute references are just in case rows are moved around for some reason.

DG Consulting

Thanks @Mike TV. I would need the formula to find the last Task 4 as it will be used in many different schedules that will have different #'s of rows. I am using the Max/Collect formula to identify the last Task 4 date as a different field (which is helpful), but I'm hoping for a formula to also show whether that last Task 4 is complete.

Mike TV

@DG Consulting

I wish you the best on a solution.

Paul Newcome

You are on the right track. You are grabbing the MAX for "Complete" already. You can use an IF statement to say that if that date is equal to the MAX date in general, then the latest one must be complete and you can output the date.

=IF(MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4", Status:Status, "Complete")) = MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4"))

DG Consulting

@Paul Newcome -- That's what I was looking for!! Thanks so much. One question though -- when I convert this formula to a real sheet it does what I want it to do when the status is Complete (which is fantastic!). When the status is not complete, however, the sheet summary field says #INVALID OPERATION. Any thoughts as to why? Thank you!!

Paul Newcome

That is because there is nothing for that first COLLECT to pull. Try an IFERROR.

=IFERROR(IF(MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4", Status:Status, "Complete")) = MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), "")

DG Consulting

@Paul Newcome -- You're awesome! Thanks so much for your help, that worked!

Paul Newcome

Happy to help. 👍️

DG Consulting

@Paul Newcome Another question came up related to this thread if you don't mind me asking. How could I adjust the formula to first confirm that ALL Task 4's in a schedule are complete and if so then return the Max Finish date?

Paul Newcome

That should be what is happening, or are they not necessarily being completed in chronological order?

DG Consulting

@Paul Newcome I agree in most cases that is what will happen -- but there are some schedules where it is possible for a later batch to complete earlier than an earlier batch due to changes along the. way and the team would like confirmation that all batches are complete before reporting the last completion date. Thanks!

Paul Newcome

Ah. Ok. So the previous formula was written assuming things would be completed in chronological order. Since that is not the case, then we need to count how many dates there are and compare it to how many dates there should be (instead of just checking to see if the last one is filled in).

=IFERROR(IF(COUNTIFS([Task Name]:[Task Name], "Task 4", Status:Status, "Complete") = COUNTIFS([Task Name]:[Task Name], "Task 4"), MAX(COLLECT(Finish:Finish, [Task Name]:[Task Name], "Task 4")), "")