Project light status, Entire Row format & due date

Taslima Afroze

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

Travis

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

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