Jul 10, 2020 09:16 AM
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
Solved! Go to Solution.
Jul 10, 2020 02:06 PM
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:
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.
IF(
{Status}='Completed',
'Complete',
IF(
{Status}='No further action',
'No further action',
DATETIME_DIFF(TODAY(),{Last Update},'d'))
)
)
Jul 10, 2020 09:37 AM
@Oliver_Atkinson - does this work for you:
The “days since update” field is:
IF(NOT(Completed), DATETIME_DIFF(TODAY(), Update, 'days'))
Jul 10, 2020 10:42 AM
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
Jul 10, 2020 12:22 PM
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?
Jul 10, 2020 02:06 PM
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:
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.
IF(
{Status}='Completed',
'Complete',
IF(
{Status}='No further action',
'No further action',
DATETIME_DIFF(TODAY(),{Last Update},'d'))
)
)
Jul 10, 2020 02:30 PM
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