Formula for late and overdue tasks

04/07/21
Accepted

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.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 04/08/21 Accepted 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:


    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

Answers

  • Heather DHeather D ✭✭✭✭✭

    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.M KhalilBassam.M Khalil ✭✭✭✭✭
    edited 04/08/21 Accepted 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:


    Best Regards

    Bassam.M Khalil


    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as the accepted answer. It will make it easier for other Smartsheet Community members to find this solution or help to answer their questions.

  • 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() > [email protected], 1, IF(TODAY() < [email protected], 0, ((NETWORKDAY([email protected], TODAY()) / (NETWORKDAY([email protected], [email protected]))))))

    Works great now!

Sign In or Register to comment.