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

Taslima Afroze
Taslima Afroze ✭
edited 12/09/19 in Archived 2015 Posts

Hi:

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!!

Tas

Tags:
· Share on FacebookShare on Twitter

Comments

  • 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")

     

    · Share on FacebookShare on Twitter
  • Taslima Afroze
    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.

    · Share on FacebookShare on Twitter
  • Taslima Afroze
    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?

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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories