Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Project light status, Entire Row format & due date



what would be the formula for the following:


project light status | Start Date | Proposed finish date | Finish date | Complete

      G                       1/1/2015           2/10/2015                     2/10/2015    complete

      R                       15/1/2015          2/10/2015                    3/15/2015     overdue/ At risk



in both cases, Start  date is pedecessaor to Proposed finish date and Finish date. So when we have a different finish date the following start will roll accordingly.

Also want to change the color of the project light accodring to the finish date. if finish day is over proposed date the light will turn red.


the formula might be very simple, but I'm very new to smartsheet. 


Thanks for the help!!



  • Travis
    Travis Employee

    Hi Tas! To confirm, you would like the RYG ball to be red if Finish Date is later than Proposed Date and green if it isn't?


    Here it is:


    =IF([Finish Date]4 > [Proposed Date]4, "Red", "Green")


  • Taslima Afroze

    I think i'm making some mistake in the formula:


    =IF([Actual Finish Date]16 > [Proposed Finish Date]16, "Red", "Yellow", IF(Complete16 = 1, "Green")))


    but it is says imbalanced. My understanding is, the later part is missing logic, but i don't know how to set that up.

  • Taslima Afroze

    thanks Travis. It worked good. one small thing that i changed is if finish date<pro date then yellow.

    here i want to add another condition, i want to check off the task by a check box and then the ball will turn green.


    so the logic is,

    if finish>prop, red, yellow, 

    and if checkbox is checked then green.


    Can i add this formula here?

  • Travis
    Travis Employee

    You are close with that formula! First, #IMBALANCE means you have too many parenthesis on the end of your formula (its an imbalance of parenthesis).


    As for the formula, switch it around so the checkbox IF statement is at the beginning. Like this:


    =IF(Complete16 = 1, "Green", IF([Actual Finish Date]16 > [Proposed Finish Date]16, "Red", "Yellow")

This discussion has been closed.