Help

How to do a Min formula with logical values

Topic Labels: Formulas
Solved
Jump to Solution
2247 5
cancel
Showing results for 
Search instead for 
Did you mean: 
Oliver_Atkinson
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions

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.

  1. 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'))
  )
)

See Solution in Thread

5 Replies 5

@Oliver_Atkinson - does this work for you:

Screenshot 2020-07-10 at 17.36.21

The “days since update” field is:

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

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

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?

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.

  1. 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'))
  )
)

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