Depending on In Progress Status and Due Date, Formula Change

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!

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

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

Where did I go wrong?

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"

)

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 -

IF(

   AND(

      OR(

         {Completion Status} = "In Progress",

         {Completion Status} = "Roadblocked"

      ),

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

   ),

   "Past Due",

   "On Track"

)

@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"

)

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.

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

@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?

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.

You guys are the best! Thank you very much, this worked for me!

One more question - building off of this, once the status is changed to Recently Completed or Completed/Archived (regardless of Target Completion Date), I’d like it to read Done​.

Also, if there is not a Target Date filled in on the Target Completion Date field, I’d like the On Track column to read “TARGET DATE NEEDED”

@Rachael_Castelaz,

I’m making some assumptions here, since what you are aiming for and what you are showing us in your screenshots seem to be a bit of a moving target – but try this and see if it’s getting you what you want:

IF(

  {Target Completion Date} = BLANK(),

  "TARGET DATE NEEDED",

  IF(

    OR(

      {Status} = "Recently Completed",

      {Status} = "Completed/Archived"

    ),

    "Done",

    IF(

      AND(

        OR(

          {Status} = "In Progress",

          {Status} = "Roadblocked"

        ),

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

      ),

      "OVERDUE",

      "‍♂️In-progress‍♂️"

    )

  )

)

You have a few other options in {Status} that are unaccounted for here, but I’m not sure what you want done with those, so I left them out.

Thank you!! It’s almost there. The “TARGET DATE NEEDED” worked perfectly. However, you’ll see in the Completed / Archived status (Recently Completed status not shown) that TARGET DATE NEEDED still appears. I would like to have the “On Track?” field read “ Done​” if it’s in the Recently Completed or Completed/Archived status, regardless of if the Target Completion Date field is empty.

Here’s how I have the code written

IF({Target Completion Date} = BLANK(),“ TARGET DATE NEEDED​”,IF(OR({Status} = “Recently Completed”,{Status} = “Completed /Archived”), “ Done​”, IF(AND(OR({Status} = “In Progress”,{Status} = “Roadblocked”),IS_BEFORE({Target Completion Date}, TODAY())),“ OVERDUE​”,“ :running_man: ‍♂In-progress​:running_man:‍♂”)))

Thanks, @Jeremy_Oglesby!! I actually just downloaded VS Code onto my Mac, and I like it better than BBEdit! It seems to work much better in terms of balancing out formulas! So I think I’m going to start using VS Code! Thank you for the tip!

IF(

  OR(

    {Status} = "Recently Completed",

    {Status} = "Completed/Archived"

  ),

  "Done",

  IF(

    {Target Completion Date} = BLANK(),

    "TARGET DATE NEEDED",

    IF(

      AND(

        OR(

          {Status} = "In Progress",

          {Status} = "Roadblocked"

        ),

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

      ),

      "OVERDUE",

      "‍♂️In-progress‍♂️"

    )

  )

)

Beautiful. I can’t thank you enough!!