Formula for late and overdue tasks

Derek Corneil
Derek Corneil ✭
in Formulas and Functions

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

· Share on FacebookShare on Twitter

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:

    1.jpg


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

    · Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter
  • 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:

    1.jpg


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ 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"

    · Share on FacebookShare on Twitter
  • Derek Corneil
    Derek Corneil ✭

    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!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!