May 15, 2020 07:34 AM
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!
Solved! Go to Solution.
May 18, 2020 01:41 PM
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.
May 18, 2020 01:20 AM
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!
May 18, 2020 09:07 AM
Hm, I couldn’t seem to make it work.
Where did I go wrong?
May 18, 2020 09:33 AM
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"
)
May 18, 2020 09:38 AM
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 -
May 18, 2020 09:39 AM
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"
)
May 18, 2020 09:41 AM
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.
May 18, 2020 09:42 AM
Ah, thanks! Looks like I was missing one parenthesis there. :slightly_smiling_face:
May 18, 2020 09:47 AM
@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?
May 18, 2020 10:05 AM
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.
May 18, 2020 11:14 AM
You guys are the best! Thank you very much, this worked for me!
May 18, 2020 11:32 AM
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”
May 18, 2020 12:00 PM
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.
May 18, 2020 01:32 PM
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:♂”)))
May 18, 2020 01:41 PM
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.
May 18, 2020 02:21 PM
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:
May 19, 2020 06:20 AM
Beautiful. I can’t thank you enough!!