Skip to main content
Solved

How to do a Min formula with logical values

  • July 10, 2020
  • 5 replies
  • 84 views
O

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

Best answer by JonathanBowen

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

5 replies

JonathanBowen
Forum|alt.badge.img+18

@Oliver_Atkinson - does this work for you:

The “days since update” field is:

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

O
O

@Oliver_Atkinson - does this work for you:

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

O

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?

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • Answer
  • July 10, 2020

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'))
  )
)
O

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

Terms & ConditionsAccessibility statement