Roll Up Min(Value) with a filter

[Oliver_Atkinson]

So I have a column titled “Days Since Update” which is the days since a certain date to today and then “Field 10” is based on an If Formula (if the status is completed it gives a 0).

I now want a formula column which gives the smallest of these two values. I’ve tried Min formula however this ignores logical values (values in field 10).

Does anyone have any suggestions?
All I really want is that when the status is completed a value doesn’t come up in Days Since Update!
Is there a way I can merge the Min and If formula together to achieve this?
image|281x500

[JonathanBowen]

@Oliver_Atkinson - does this work for you:

The “days since update” field is:

IF(NOT(Completed), DATETIME_DIFF(TODAY(), Update, 'days'))

[Oliver_Atkinson]

Hi Jonathan,

I think this would be absolutely perfect however my completed column is a single select field where I select ‘Completed’ how would I amend your formula to reflect this?

Really appreciate your help

[Oliver_Atkinson]

Hi Jonathan,

I’ve managed to crack it with:

IF(Status=‘Completed’,“Complete”, DATETIME_DIFF(TODAY(),{Last Update},‘d’))

However as well as ‘Completed’ i also have a status of ‘No further action’ how can I do include this as well? Can i do it with another IF Formula?

[JonathanBowen · Accepted solution]

Yeah - I did wonder if it was a single select, but as you’ve done, it isn’t too hard to adapt from the checkbox version.

A couple of options on your other question:

1. If you are OK if the two statuses result in a “Complete” value then you can say:

IF(OR(Status='Completed', Status='No further action'),'Complete', DATETIME_DIFF(TODAY(),{Last Update},'d'))

So it it is ‘Completed’ OR ‘No further action’, then show ‘Complete’, otherwise, the days calc.

2. If you want them to show different values:

IF(
  {Status}='Completed',
  'Complete',
  IF(
    {Status}='No further action',
    'No further action',
    DATETIME_DIFF(TODAY(),{Last Update},'d'))
  )
)

[Oliver_Atkinson]

That’s been so helpful- thank you!

I went with the second option which has worked really well. I only originally wanted it to show a 0 when the status is complete/no further action but this is so much better. Really appreciate all of your help

Thanks again