RAG Status by Percentage Complete and today's date




I have a formula for RAG status which I've been using to manage performance across multiple projects looking at the columns end date, % complete, and referencing today's date. However it is not working as planned and wanted to ask for some help please.

I would like the formula to simply change the RAG status for a tasks as follows:

Complete or up to 5% behind schedule = green;

5-10% behind schedule = amber;

11% or more behind schedule = red.

Current Status

The way the formula is currently working is as follows: if the task is ahead of schedule or complete it is green; if the task is behind schedule but the due date hasn't passed it is amber, if it is behind schedule and the due date has passed it is red.

Current Formula

IF(AND(TODAY() > [End Date]@row, [% Complete]@row < 0.9), "Red", IF([% Complete]@row >= ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)), "Green", "Yellow"))))

Any help would be excellent, thank you!




  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 08/22/22


    If I understand correctly, you're calculating the % behind or ahead of schedule by counting the number of days since the start date and dividing that by the scheduled number of days in the project, yes? So for instance, if I had a project starting 8/1/22 and ending 8/21/22, and today is 8/22/22, I'm at 1.05, or 5% behind schedule. But if my end date was 8/23/22, today I'd be at .95, or 5% ahead of schedule.

    Looking at your desired logic:

    Anything 100% complete OR up to 5% behind schedule should be green.

    Anything not 100% complete, AND more than 5% but only up to 10% behind schedule, should be yellow.

    Anything not fitting the above criteria (i.e. anything not yet complete and over 10% behind schedule,) should be red.

    So let's try this:

    =IF(OR([% Complete]@row = 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.05), "Green", IF(AND([% Complete]@row < 1, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) > 1.05, ((TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)) <= 1.1), "Yellow", "Red"))


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • DomG
    DomG ✭✭

    Many thanks for coming back to me Jeff.

    Unfortunately, the formula hasn't worked. When testing in one of the projects, it is showing the project, which is 30% behind schedule as green whereas based on the criteria below it should be Red. The logic you outline above is correct, however

    I have a baseline column which I compare against, Planned % Complete with a simple formula as follows:

    =IF(TODAY() >= [End Date]@row, 1, IF(TODAY() <= [Start Date]@row, 0, (TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row)))

    As you will see from the screenshot - the project should be 44% complete, but it is 11% complete and showing as Green with your formula.

    Any other suggestions would be super helpful.

    The Red / Amber / Green criteria are as follows:


    Up to 5% behind schedule vs Planned % Completion OR ahead of schedule OR complete = Green


    Between 90%-95% behind planned % completion = amber


    <90% behind planned percentage completion = red

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!