Help With Join / Collect Formula

Brian McElligott

I am trying to pull row details from a project plan sheet into another sheet, where the due date is 5 days past the current date.

Sheet 1 = Project Plan sheet, containing the Task Name and Estimated End Date

Sheet 2 = Data Sheet, where the late tasks should appear

I am using a Join / Collect function, but open to other functions.

=JOIN(COLLECT({Task Name}, {Estimated End Date}, (TODAY() - {Estimated End Date} > 5)), CHAR(10))

I am currently getting "#Invalid Operation" as a result.

Thoughts?

Courtney S.

I think your COLLECT criterion needs to change from " TODAY() - {Estimated End Date} > 5 "
to something like: <(TODAY() - 5)

Think of the Criterion part as ending the sentence "The [criterion range values] must be…"
So the sentence here is: The Estimated End Date value must be more than five days in the past AKA less than (Today - 5)

Here's the COLLECT function page for details: COLLECT Function | Smartsheet Learning Center

Brian McElligott

Thank you Courtney! Your explanation of the criterion was on point and helped me think about my applied logic in better ways. Your help has been amazing.