Filter Formula that are completed more than 3 days ago

1

Hi Guys, with regards to filter formula, Am looking to hide the rows of completed tasks automatically after 3 days of completion.

May I know why should the formula be
thisRow.Status != “Completed” OR thisRow.Modified() > (Today() - 3)
instead of
thisRow.Status != “Completed” && thisRow.Modified() > (Today() - 3)

May I know why are we using ‘OR’ instead of ‘&&’?

I need the formula to fulfill both criteria before being shown. Wouldn’t using ‘OR’ filter rows that are either completed or those that are modified more than 3 days?

Hope someone can shed some light. Thank you.

2

Hello there!

If your row is not completed, but was modified more that 3 days ago, it should also be visible, right? So, we check each row if it is not completed (and show all not-completed rows) OR check the row if it was modified less than 3 days ago, and also show it even it is completed.

For reliability I’d suggest another solution. Now if you accidentally change some value in the row, it will appear despite the Completed status. So, I’d suggest to fill some kind of Date of Completion column with the button, see example. I’ll leave all rows visible for clarification, you can easily hide them:

1 Like
3

Hi Denis, adding a date of completion totally make sense! I’ll go and implement it.

However I would like to further clarify ‘&&’ and ‘OR’.

So… if I want to show the results only when both criteria are fulfilled, I use ‘OR’.
If I want to show the the results of each single criteria, I’m supposed to use ‘&&’?

Am I right to say that?

4

So… if I want to show the results only when both criteria are fulfilled, I use ‘OR’.
If I want to show the the results of each single criteria, I’m supposed to use ‘&&’?

It is the opposite!
In your case criteria to hide are:

  1. Status in completed
  2. Row was modified more than 3 days ago, let name this Old

So, the row should be hidden if both criteria are true.
Pseudo formula will be:

Hide row = Completed AND Old

(Note that && and AND are the same, you can use && as such as AND with no difference. For the OR operator there is also || syntax)

But we want to write criteria to show row, not to hide it, because Filter formula asks us what we want to show. This criteria can be

Show row = NOT (hide row) = NOT (Completed AND Old)

And this formula will work if you try. However, we can open parenthesis. Unfortunately, we can’t just write NOT Completed AND NOT Old because boolean algebra works different.

Imagine you have box of socks, some of them are red, some are white. And also some are big and some are small. If some sock is not (red and big) it can be:

  • white and big
  • red and small
  • white and small

It is totally different from not red and not big, isnt it? These three sock types perfectly match criteria white OR small. Each of this types is at least white or at least small. Or in other words: not red OR not big. See?

NOT (red AND big) = NOT red OR NOT big

yes, sounds crazy :slight_smile: Is it called De Morgan’s laws

Back to your data:

Show row = NOT (hide row) = NOT (Completed AND Old) = NOT Completed OR NOT Old

5

Hi Denis,

Thanks for spending your time and effort on explaining the concept to me. Really appreciate it.

I recalled that I had understood this concept easily during school days. Not sure why it seems much complicated than it used to be. lol.

So after reading your explaination. I have concluded that ‘Or’ can be rephrased using ‘At least’.

‘(xx And xx)’ can be rephrased as ‘together at the same time’.

Is my understanding correct?

Thanks!

6

Yes, you’re right, it can be rephrased this way :slight_smile:

You are welcome!

7

hey there!

I have the same need but don’t understand the solution. Could someone kindly share the final formula and indicate where to include it in my table?

Thank you very much in advance, and sorry for my ignorance.