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