Task Status Formula based on Sub-Task Completion

[K3ssen]

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

[augmented]

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.

[K3ssen]

[K3ssen]

[K3ssen]

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”

[K3ssen]

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

[Jeremy_Oglesby]

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”
   )
)

[K3ssen]

Jeremy_Oglesby:

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

[K3ssen · Accepted solution]

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