Creating a formula to show status based on time of input date

Hello all,

I am trying to create a formula. I need the status of a field to change color to indicate when its time to perform a task, which is based off of the input date and would change two weeks after. For example, if the task was done 08/14/23, that date would be added manually to the row and the status in the row would automatically indicate green. In two weeks on 08/28/23, the status would the change to red automatically to know that its time to perform the task again. Could someone assist?



  • Or is it even possible to have the formula include a checkbox rather than a manually added date? Would the date of change of checking off a box be enough to create a formula like this?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    Hi @slsny support,

    There are a few options here, are you wanting to use a status column (for example, a traffic light system) or do you want to colour the relevant cells/rows?

    The former would use a formula to switch a status light from green to red when 14 days is reached, such as:

    =IF(TODAY() - [Last done]@row >= 14, "Red", "Green")

    Colouring the row/cells would be done via conditional formatting:

    Combined results:

    You can add more complexity to this if required, but this should hopefully point you in the right direction to start.

    Any questions or comments, then just ask! 😊

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!