Depending on In Progress Status and Due Date, Formula Change
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?
That’s the structure anyway I haven’t tested this - So you may need to play around to get it working.
Hope that helps!
Hm, I couldn’t seem to make it work.
Where did I go wrong?
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 -
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():
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.
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?
@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!
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”
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.
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.
I just swapped the positions of the "Done" and "TARGET DATE NEEDED" conditional blocks, so that "Done" takes precedence.
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.
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!
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.
Done
TARGET DATE NEEDED
OVERDUE
♂️In-progress