Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Formula for calculating Current Date/Status and Expected end date

    ✭✭✭✭
    in Formulas and Functions

    I am trying to get my Status Column to be the following colours below based on the criteria.

    Grey = Status is “Not Started”

    Yellow = Status = “In Progress” and current date is less than expected finish date

    Red = Status = “In Progress” and current date is greater than expected finish date

    Green = Status = “Completed

    Any ideas of what this formula would look like, I have been trying to get this to work but it is not picking up the expected finish date.

    For the current date I am using the "today" formula so it is consistantly updated.

    Any assistance would be appreciated.

    Thank you

    · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭

      Hi @Roshni Verappan

      Try something like this

      =IF(status="Not Started", "Grey", IF(AND(status="In Progress", Current Date<Expected Date),"Yellow", IF(AND(status="In Progress", Current Date>Expected Date),"Red", IF(status="Completed", "Green")))


      Thanks & Regards

      Khasim

      SSPM Consultants

      Email ID: info@sspmconsultants.com

      Did I answer to your question or fix the problem? Please help the Smartsheet Community by voting it Insightful/Vote Up/Awesome, or/and Accepted Answer. It will make it easy for others to discover a solution or help in answering!

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Thanks Khasim that formula worked but only for the 'Not Started' I am thinking I might need to add the OR or And function for it to work for the other conditions.

      This is a great start for me to get it working.

      thank you

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions