Adding to a X-Sheet INDEX(MAX(COLLECT( Formula

The original formula I had works:

INDEX(MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1)), 1, 0)

Now I am trying to add an IF statement and am getting an #InvalidOperation error. Can you recommend a change to the formula?

I am trying to add an IF statement that looks at a date column the reference. If the date is not within the last 7 day

=IF(TODAY() - 7 < {FDOB Change Log - PROJECT CREATED DATE}, INDEX(MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1)), 1, 0), "")

Find more posts tagged with

Formulas

if formula

Accepted answers

All comments

Paul Newcome

Try this:

=MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1, {FDOB Change Log - PROJECT CREATED DATE}, @cell < TODAY(-7)))

Smar Czar

@Paul Newcome Thank you! I didn't get an error with that formula but did not get the result I was expecting. Just reading my original post it looks like there was an an incomplete thought there….

If the date is not within the last 7 days, then the value should be "". if the date is within the last 7 days and the checkbox in the column called DATE CHANGE is checked.

I will keep tinkering.

Paul Newcome

In that case, give this a try:

=MAX(COLLECT({Change Log - ADDRESS Prior DATE}, {Change Log - ADDRESS DATE CHANGE}, 1, {FDOB Change Log - PROJECT CREATED DATE}, @cell >= TODAY(-7)))

Smar Czar

He shoots, he scores! Thank you, @Paul Newcome

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.