Formula for late and overdue tasks

Derek Corneil

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

Bassam Khalil

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:

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

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:

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!