Estimate End Date, Actual End Date, Adjusted End Date flagging

Michele Deo
Michele Deo ✭✭
edited 12/09/19 in Formulas and Functions

For my projects I track Estimated/Actual Start and Estimate/Adjusted/Actual End dates.  I need to have a status flag indicate yellow if the Adjusted date is slipping past the estimated end date, and then when the end date is adjusted and justified, to update the status flag back to green.

Is anyone tracking like I am?  Do you have a formula that works for you that you'd be willing to share?


  • if(adjusted>estimated_end,"Red", "Yellow")

  • Thank you, but I forgot some elements in the formula.  If the adjusted end date is greater than 90 days passed the Estimated End day, then highlight the Project Name in Yellow.  If it's later than 90 days passed then turn project Name red.

    I think I can get this, but not sure, but would it be something like...


    if(adjusted_end_date>estimated_end+90, Project Name="Yellow", (if(adjusted_end_date>Estimated_end+100, Project Name="Red")))


    Is that right?


  • syntax looks ok.  

    you originally said you wanted a status flag. If so then this code goes in the status flag column

    if(adjusted_end_date>estimated_end+90, "Yellow", (if(adjusted_end_date>Estimated_end+100, "Red","Green")))

    if you want to change the color of the project name, do this in conditional formatting (and then you don't need the part about the colors), you just do the condition and then select the formatting


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!