Formula for late and overdue tasks

Hi Community,

I'm new to Smartsheet and looking to replicate a formula I used to use in another software product. I would like to create a health indicator that will tell me the following:

  • If task is 100% complete - Blue indicator
  • If task % complete is GREATER than task % duration complete - Green indicator (e.g 50% work complete but only 20% duration passed)
  • If task % complete is LESS than task % duration complete - Yellow indicator (e.g. 20% work complete and 50% duration passed)
  • If task % complete is LESS thank 100% and today is GREATER than Finish date - Red indicator

The first and last are straight forward enough, but I'm struggling with the middle two since I'm not as familiar with the field names. Any help you could provide would be greatly appreciated.

Thanks,

Derek

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/08/21 Answer ✓

    Hi @Derek Corneil

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IF(ISBLANK([% complete]@row), "", IF(AND(TODAY() > [Finish Date]@row, [% complete]@row < 1), "Red", IF([% complete]@row = 1, "Blue", IF(AND([% complete]@row < 1, [% complete]@row > [% duration passed]@row), "Green", IF(AND([% complete]@row < 1, [% complete]@row < [% duration passed]@row), "Yellow")))))

    please use the same column names & format as i use, the following screenshot shows the result and you can test it using the following published sheet:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭

    Hi Derek,


    Let's assume your % complete column is called "%Complete", your % duration is called "%Duration", and your finish date column is called "Finish". Here's what you would put in your RYGB column:

    =if([%complete]@row=1,"Blue",if([%complete]@row>[%duration]@row,"Green",if[%complete]@row<[%duration],"Yellow",if(and([%complete]@row<1,TODAY()>[finish]@row),"Red",""))))

    Of course, if your columns have different titles, you'll want to replace my column titles with yours.


    Hope this helps. Let me know if it works!

    Best,

    Heather

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 04/08/21 Answer ✓

    Hi @Derek Corneil

    Hope you are fine, please try the following formula and convert it to column format formula:

    =IF(ISBLANK([% complete]@row), "", IF(AND(TODAY() > [Finish Date]@row, [% complete]@row < 1), "Red", IF([% complete]@row = 1, "Blue", IF(AND([% complete]@row < 1, [% complete]@row > [% duration passed]@row), "Green", IF(AND([% complete]@row < 1, [% complete]@row < [% duration passed]@row), "Yellow")))))

    please use the same column names & format as i use, the following screenshot shows the result and you can test it using the following published sheet:


    PMP Certified

    [email protected]

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hi Heather and Bassam. Thank you for your assistance. I was able to get it to work using Bassam's formula. I also automated the % Duration so that I don't have to manually enter it.

    =IF(TODAY() > Finish@row, 1, IF(TODAY() < Start@row, 0, ((NETWORKDAY(Start@row, TODAY()) / (NETWORKDAY(Start@row, Finish@row))))))

    Works great now!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!