Help

Re: Requesting help with conditional IF formula for makeshift status field

Solved
Jump to Solution
466 0
cancel
Showing results for 
Search instead for 
Did you mean: 
AnnicaT
7 - App Architect
7 - App Architect

Hello!
I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance.

I have the following columns that should be considered in the formula:
{End Date} = Formula field
{BiU} = Checkbox field
{Last Shared} = Date field
{Next Share} = Formula field

What I want to achieve is:
If {BiU} is NOT checked = ‘-’
If {BiU} is checked AND TODAY() is after {End Date} = :white_check_mark:
If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = :ballot_box_with_check:
If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = :link:

Any and all help would be greatly appreciated.

44 Replies 44

We have a WINNER!!!

Yay!, You’re amazing.
Thank you sooo much for spending so much time on this and helping even when I confuse myself.
I don’t know how I’ll ever repay you, but if nothing else I’ll try to pay it forward.

Oh my goodness, yay!! You’re so welcome, and thank you for taking the time to explain in detail - that’s what really did the trick. I was getting confused on what dates would be expected to fall in what order, so your final explanation was extraordinarily helpful toward correcting and simplifying the formula.

Good luck and don’t hesitate to shoot me a message if you need help in the future! :slightly_smiling_face:

Hello, @AlliAlosa !
Please help me with my issue.
I’m gonna filter records on this:
$filtered_row = $airtable->getContent( ‘Invoiced’, [‘filterByFormula’ => “IS_SAME({LastModifiedTime}, ‘. $today_date .’)”]);

In other words, I’m gonna compare the “LastModifiedTime” field in record with today date.

How can I do this?

Alicia_Bethel
4 - Data Explorer
4 - Data Explorer

I have something similar but not quite as complicated, but I’m still struggling LOL

I need the following:
If Phase/Status is Cancelled or Complete AND Last Modified is more than 14 days ago, formula field should read “Remove From Digest”, otherwise, BLANK.

Use case: I’m sending an automated email of our project pipeline and if something is cancelled/Complete and we don’t update the record for 2 weeks, we want it to drop off the view that is generating the email if that makes sense. I.e. we are only seeing cancelled/complete projects for a certain amount of time and not for the life time of our airtable use.

Let’s divide this into chunks…

This could be done a couple different ways. The first option that many will attempt would be something like this:

OR({Phase/Status} = "Cancelled", {Phase/Status} = "Complete")

However, it could also be done this way:

REGEX_MATCH({Phase/Status}, "Cancelled|Complete")

I’ll use the latter option going forward, largely because it’s shorter.

Next chunk:

For this we’ll compare LAST_MODIFIED_TIME() against NOW() using DATETIME_DIFF():

DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14

Each of those expressions will return True or False. To test if they’re both true, we can combine their logic using the AND() function:

AND(
  REGEX_MATCH({Phase/Status}, "Cancelled|Complete"),
  DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14
)

Finally we’ll wrap that into an IF() function to create the desired output if both are True:

IF(
  AND(
    REGEX_MATCH({Phase/Status}, "Cancelled|Complete"),
    DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "days") > 14
  ),
  "Remove From Digest"
)

While some might be tempted to add the BLANK() function as a third argument in the IF() function, it’s not necessary. The IF() function is already designed to not return anything if that argument is omitted.