Skip to main content
Solved

Task Status Formula based on Sub-Task Completion

  • March 10, 2021
  • 8 replies
  • 84 views
K

I’ve built a formula that shows a Status in my Task field based on how many Sub-Tasks are complete:

IF({Sub-Tasks Complete} = 0, “ :radio_button: Not Yet Started”, IF({Sub-Tasks Complete} = {# of Sub-Tasks}, “ :white_check_mark: Complete”, “ :orange_circle: Working On It”))

For some of my Tasks there is only one Sub-Task. When one of my teammates moves the Sub-Task into the “Working On It” status, I would like the status in the Task field to say, “Working On It.” Not sure how to change my formula to do that. Any suggestions?

Thanks,

Kevin

Best answer by K3ssen

Jeremy - thanks for the formula. I added the roll-up field you suggested and the formula is showing as invalid.

Here’s my roll up field:
Am I missing something? Is that Aggregation formula correct? thanks again for your help on this!

Kevin


UPDATE - I put another space between the “IF(” and the “AND” and now the formula works…so thanks Jeremy!!

8 replies

A
Forum|alt.badge.img+18
  • augmented
  • Inspiring
  • March 10, 2021

I think this is a case where a picture of your table, and the relevant fields, would probably help. Just to see what types of fields you are referring to. I’m assuming rollups for some of these, but not sure.

K
  • K3ssen
  • Author
  • Inspiring
  • March 12, 2021

K
  • K3ssen
  • Author
  • Inspiring
  • March 12, 2021


K
  • K3ssen
  • Author
  • Inspiring
  • March 12, 2021

So the first image, shows the fields I use to calculate the status. The second image is how I’d like it to appear to my client. When there is only 1 sub-task for a Task, it reads as “Not Yet Started”

K
  • K3ssen
  • Author
  • Inspiring
  • March 12, 2021

So the first image, shows the fields I use to calculate the status. The second image is how I’d like it to appear to my client. When there is only 1 sub-task for a Task, it reads as “Not Yet Started”


My team uses Kanban to change status from Not Yet Started to Working on It. We use a single select for this.

J
Forum|alt.badge.img+18

Hi @K3ssen

I think you’re going to need another Rollup field, in addition to your {Sub-Tasks Complete} field. You’ll need one for {Sub-Tasks in Progress} that Rolls up Sub-Tasks where Status = "Working On It".

Then you can adjust your Task Status formula like so:

IF(
   AND({Sub-Tasks Complete} = 0, {Sub-Tasks in Progress} = 0), 
   “🔘Not Yet Started”, 
   IF(
      {Sub-Tasks Complete} = {# of Sub-Tasks}, 
      “✅Complete”, 
      “🟠Working On It”
   )
)
A
K
  • K3ssen
  • Author
  • Inspiring
  • March 15, 2021

Hi @K3ssen

I think you’re going to need another Rollup field, in addition to your {Sub-Tasks Complete} field. You’ll need one for {Sub-Tasks in Progress} that Rolls up Sub-Tasks where Status = "Working On It".

Then you can adjust your Task Status formula like so:

IF(
   AND({Sub-Tasks Complete} = 0, {Sub-Tasks in Progress} = 0), 
   “🔘Not Yet Started”, 
   IF(
      {Sub-Tasks Complete} = {# of Sub-Tasks}, 
      “✅Complete”, 
      “🟠Working On It”
   )
)

Jeremy - thanks for the formula. I added the roll-up field you suggested and the formula is showing as invalid.

Here’s my roll up field:
Am I missing something? Is that Aggregation formula correct? thanks again for your help on this!

Kevin

K
  • K3ssen
  • Author
  • Inspiring
  • Answer
  • March 15, 2021

Jeremy - thanks for the formula. I added the roll-up field you suggested and the formula is showing as invalid.

Here’s my roll up field:
Am I missing something? Is that Aggregation formula correct? thanks again for your help on this!

Kevin


UPDATE - I put another space between the “IF(” and the “AND” and now the formula works…so thanks Jeremy!!

J
Terms & ConditionsAccessibility statement