Formula to return boolean looking at multiple date columns

I need a formula that will look at three date columns and if any of them are date that isn't in the last 5 days, I want a boolean '1' returned.

This is what I've used:

=IF([Project Health RAG Last Updated]@row <TODAY(-5) OR(IF([Current Position Last Updated]@row <TODAY(-5), OR(IF([Next Actions Last Updated]@row <TODAY(-5), 1)))))

But it doesn't like it.

TIA

Cheryl

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

That's because all three columns are blank so the MIN/COLLECT is not pulling anything in to compare to TODAY(-5).

Try this to get rid of the error on rows where all three are blank...

=IFERROR(IF(MIN(COLLECT([Project Health RAG Last Updated]@row:[Next Actions Last Updated]@row, [Project Health RAG Last Updated]@row:[Next Actions Last Updated]@row, @cell <> "")) < TODAY(-5), 1), "")

All comments

Paul Newcome

Try this:

=IF(MIN(COLLECT([Project Health RAG Last Updated]@row:[Next Actions Last Updated]@row, [Project Health RAG Last Updated]@row:[Next Actions Last Updated]@row, @cell <> "")) < TODAY(-5), 1)

Cheryl Collins

Hi @Paul Newcome

Thanks for this. I've added it in but, it's coming back as #UNPARESABLE

I've tried just copying and pasting your formula as well as just re-typing but neither way work.

Thanks

Cheryl

Paul Newcome

In the first range you need to replace the 1 after the column name with @row. Then the second range needs to have the second colon as well as the orange bit removed.

Cheryl Collins

Hi @Paul Newcome

Thanks - I've updated as instructed but now getting a #INVALID OPERATION error

Paul Newcome

That's because all three columns are blank so the MIN/COLLECT is not pulling anything in to compare to TODAY(-5).

Try this to get rid of the error on rows where all three are blank...

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.