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!

[Rachael_Castela]

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

[Rachael_Castela]

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 :white_check_mark: Done​:white_check_mark:.

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”

[Jeremy_Oglesby]

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

[Rachael_Castela]

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 “ :white_check_mark: Done​:white_check_mark:” 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(),“ :stop_sign: TARGET DATE NEEDED​:stop_sign:”,IF(OR({Status} = “Recently Completed”,{Status} = “Completed /Archived”), “ :white_check_mark: Done​:white_check_mark:”, IF(AND(OR({Status} = “In Progress”,{Status} = “Roadblocked”),IS_BEFORE({Target Completion Date}, TODAY())),“ :alarm_clock: OVERDUE​:alarm_clock:”,“ :running_man: ‍♂In-progress​:running_man:‍♂”)))

[Jeremy_Oglesby · Accepted solution]

Let’s try this:

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🏃‍♂️"
    )
  )
)

I just swapped the positions of the "✅Done✅" and "🛑TARGET DATE NEEDED🛑" conditional blocks, so that "✅Done✅" takes precedence.

[ScottWorld]

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…

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! :slightly_smiling_face:

[Rachael_Castela]

Beautiful. I can’t thank you enough!!