I need a formual to return a true or false if two criteria are met, including a date comparison

Andrew Verderame

Seeking to have a formula that returns a true or false (1 or 0) if two criteria are met from rows and columns within the same sheet

One, does another row have the same "Site" (column) as the row in question?

Two, does the "Date Completed" (column) of that row {with the matching Site} have a date within the last 30 days of the "Date Completed" cell of the row in question?

Thus in summary if another row has the same Site and also has a Date Completed within 30 days of that same row then I would want a "true" output.

I tried this formula but I receive #INVALID OPERATION error in the cell:
=IF(COUNTIFS([Site]:[Site],[Site]@row, [Created Date]:[Created Date], AND([Created Date]@row - 30 <=[Created Date]:[Created Date], [Created Date]@row + 30 >= [CreatedDate]:[Created Date])) > 1, true, false)

Can anyone help? Thank you

AdamSYNH

Hi @Andrew Verderame,

Try this:

Formula:

=IF(COUNTIFS(Site:Site, Site@row, [Created Date]:[Created Date], <([Created Date]@row + 31), [Created Date]:[Created Date], >([Created Date]@row - 31)) > 1, true, false)

AdamSYNH

Hi @Andrew Verderame,

Try this:

Formula:

=IF(COUNTIFS(Site:Site, Site@row, [Created Date]:[Created Date], <([Created Date]@row + 31), [Created Date]:[Created Date], >([Created Date]@row - 31)) > 1, true, false)

Andrew Verderame

Thank you @AdamSYNH that seems to have worked! Grately appreciate it

AdamSYNH

You're most welcome @Andrew Verderame, please do mark the response as best answer so that others can find it when searching for similar questions :)