Depending on In Progress Status and Due Date, Formula Change

[Rachael_Castela]

Hey there,

I have created single drop down statuses for my projects I’m working on (In Progress, Not Started, Recently Completed, Roadblocked, and Archived). I’d like to create a formula that says "If the project status is anything other than Archived and Recently Completed, AND has a past Target Completion Date that’s before today, status is: PAST DUE. Otherwise, ON TRACK.

Ideally the way I’d like this to show up is to have another column with PAST DUE and ON TRACK options in a single select drop down, and depending on what the formula says, the drop down option changes. Is that possible?

Thank you!

[andywingrave]

Hey there! This is definitely possible

IF(OR({project status} != "Archived" OR project status} != "Recently completed"), "Past Due",   "On Track")

That’s the structure anyway I haven’t tested this - So you may need to play around to get it working.

Hope that helps!

[Rachael_Castela]

Hm, I couldn’t seem to make it work.

Where did I go wrong?

[Jeremy_Oglesby]

Hi @Rachael_Castelaz — I think @andywingrave had some typos in his formula there.

OR() is a function, so it has to wrap the arguments it is operating on — it can’t be used inline like that. Here’s a revised version of your formula that also checks Target Completion Date against TODAY():

IF(
   AND(
      OR(
         {Completion Status} = "In Progress",
         {Completion Status} = "Roadblocked"
      ),
      IS_BEFORE({Target Completion Date}, TODAY())
   ),
   "Past Due",
   "On Track"
)

[andywingrave]

Thank you @Jeremy_Oglesby ! Answering these questions on my phone is never a good idea. I never write these formulas from memory in real life, I don’t know why I think I can do it for the forum -

[ScottWorld]

@Jeremy_Oglesby,

It looks like she is also trying to figure out if the Target Completion Date is earlier than today.

Can you wrap that OR function within an AND function like this?

I ask, because this formula isn’t working for me that I just typed up:

IF(
AND(

OR(
{Completion Status} != "In Progress",
{Completion Status} != "Roadblocked"
),

IS_BEFORE({Target Completion Date},TODAY()),

"Past Due", "On Track"
)

[Jeremy_Oglesby]

Ya, I should have read her original question more thoroughly before posting, instead of just her reply :winking_face:

I fixed my response to account for this.

[ScottWorld]

Ah, thanks! Looks like I was missing one parenthesis there. :slightly_smiling_face:

[ScottWorld]

@Jeremy_Oglesby, is there a special trick you use to get BBEdit to show you when you are missing a parenthesis? In other words, a way for BBEdit to show you when your formula is unbalanced?

[Jeremy_Oglesby]

I don’t use BBEdit, @ScottWorld – I use VS Code myself.

VS Code will automatically create a closing parenthesis for me whenever I type an opening parenthesis – so I don’t have to worry about forgetting to close them. I’m sure BBEdit must have a setting that allows this also, as it’s a very robust text editor…

If I get a chance to install BBEdit and check it out today, I’ll report back if I find anything.