Index Match or Max Collect Formula?

What about using an IF statement instead of IFERROR? Check to see if there is no Max Due Date, and if the MAX is 0 (meaning there is no Max), return the Initial Due Date. If not, return the original MAX(COLLECT.

Something like:

=IF(MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)) = 0, Initial Due Date@row, MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)))

It means repeating your MAX(COLLECT - once to find out if there is anything in that column for your criteria, then once to actually return the value if it's not blank. Let me know if this works for you!

Cheers,

Genevieve

Hey @Genevieve P.

Thank you, I really appreciate you taking the time to answer! That almost worked, but I kept getting the #INVALIDOPERATION error if it is a project number that does actually have an updated due date. If it did not have an updated due date, it put in the Initial Due Date.

So with your help I was able to come up with the working formula which ended up being, =IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row).

Thank you again :)

@Genevieve P.

Thank you again for your help :) With the new formula I kept getting Invalid Column Value, even tho all the column types were matching. But I used the new formula you gave me and then another one you provided on a different discussion and was able to come up with the right formula!! Thank you for answering everyones questions :) The final formula turned out to be:

=IF(HAS({ProjectNumberColumn}, ProjectNumber@row), INDEX(COLLECT({UpdatedDueDateColumn}, {CreatedDateColumn}, MAX(COLLECT({CreatedDateColumn}, {ProjectNumberColumn}, Projectnumber@row))), 1), [Initial Due Date]@row)

All comments

Something like:

=IF(MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)) = 0, Initial Due Date@row, MAX(COLLECT(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)))

Cheers,

Genevieve

Hey @Genevieve P.

Thank you again :)

Wonderful! I'm glad you were able to find a solution that worked. 🙂

@Genevieve P.

Hey! I am hoping you might be able to help me again. So, the formula worked, except for it is finding the Max Updated Due Date, instead of using the Latest entry, which would be the Max Created Date. Sometimes the updated due date is earlier, which is why I need to base the Max from the Created Date column, so it captures the latest entry.

How can I make my formula give me the Updated Due Date, that Matches the Project Number, with the most recent Created Date? I tried to add an additional criteria range to the collect, but I keep getting unpareseable error. Would love any help or suggestions!

I tried: =IF(HAS({ProjectNumbercolumn}, projectnumber@row), COLLECT({UpdatedDueDateColumn}, {ProjectNumbercolumn}, projectnumber@row, {CreatedDatecolumn}, Max({CreatedDatecolumn})), [Initial Due Date]@row))

It's finding the Updated Due Date as that's the first range listed after MAX(COLLECT in your original formula. We just need to swap that out to be your Created Date column, instead!

Original:

=IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(UpdatedDueDate, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row)

New:

=IF(HAS(ProjectNumberColumn, ProjectNumber@row), Max(Collect(CreatedDateColumn, ProjectNumberColumn, ProjectNumber@row)), InitialDueDate@row)

Cheers,

Genevieve

@Genevieve P. yes, but wouldn't that give me the created Date instead of the updated Due Date? I would still like it to display the value of the Updated Due Date, but with the most recent Created Date.

Here is an example, see screenshot below: For Project 3223, the answer should be 4/11/22, based on the newest created date.

Here is what is submitted for the project:

Screen Shot 2022-04-06 at 10.31.46 AM.png

Here is what I pull up with the Formula:

Screen Shot 2022-04-06 at 10.27.59 AM.png

Thank you for clarifying! I understand now; you'd like to use the MAX Created Date as another condition/filter to bring back the Updated date.

You were close with your previous formula then. However when we're looking for the MAX date, we'll need to use a MAX COLLECT again because we're looking for the MAX date of the same Project Number.

MAX(COLLECT({CreatedDatecolumn}, {ProjectNumbercolumn}, projectnumber@row))

Try something like this:

=IF(HAS({ProjectNumbercolumn}, projectnumber@row), COLLECT({UpdatedDueDateColumn}, {ProjectNumbercolumn}, projectnumber@row, {CreatedDatecolumn}, MAX(COLLECT({CreatedDatecolumn}, {ProjectNumbercolumn}, projectnumber@row))), [Initial Due Date]@row)

Cheers,

Genevieve

@Genevieve P.