Formula to return boolean looking at multiple date columns

Cheryl Collins

Hi

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

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), "")

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...

=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), "")